Some Excel help needed

[evild]

I have 2 spreadsheets.
Both have the same columns except for an additional 2 columns of data on spreadheet 1.

I want to import the data that is in spreadsheet 2 into spreadsheet 1 and add default values into the two extra colmns.

for example I may have:-

spreadsheet 1:
name address number status letter no.
john yavin 0800 NC 1
james vulcan 0870 NCUR 2

spreadsheet 2:
name address number
sam romulus 0999

I want to have spreadsheet 1 import the data in spreadsheet 2 such that:
spreadsheet 1=
name address number status letter no.
john yavin 0800 NC 1
james vulcan 0870 NCUR 2
sam romulus 0999 NC 1

Note the extra data from the status and letter column. I want default values to be added when the data is inserted.

What is the best wasy to achieve whis using a macro [i.e. vb]? Should I open spreadsheet 2 while in spreadsheet 1 and copy/paste lines etc using code?

I want also to check that an entry does not already exist before inserting. If there is an entry already it will not insert that line data and move to the next.

The reson for this is that a new spreadsheet is created once a month and I want to create a macro that will update a current spreadsheet with the new data.

Some pointers would be great. As well as some vb help

 

[evild]

any help would be great

 

[evild]

I know you people here must have some idea how to do this...

 

[BoulderBum]

The VB forum would probably be more helpful.

I'd recommend learning about ranges for selecting then inserting the data.

If you'd prefer to keep things simple (and sorta slow), you can select a row at a time, look for a key value using Microsoft's built-in find function, skip the insert if the value already exists, but add the data and the default values if a duplicate isn't found.

I forget the exact funtions you want, but it's usually pretty simple to figure out what you need by recording a macro, then looking at its code to find the objects you want. From there, you can look up the VBA help file associated with the object (which is sometimes easiest by tracking it down in the object browser).

That said, there may be pre-made functions that would simplify the importing process. Check the VBA Visual Basic for Applications (Microsoft) forum for additional advice.

 

[dfadfdf]

erwtyrete

 

[evild]

I have posted to the VB forum as suggested. Thanks for the pointer...

 

[BoulderBum]

No prob.