Export data from one excel to another based on a common ID using macro instead of Vlookup

[Deepa12]

Hi Experts,
I have had valued experts helping me before. I request there help now too, to solve mine as macros are new to me.
I have a Master file and a Monthly file.
I need to export certain column data from Master to Monthly file based on a common ID " CostCentre+ B ID", available in both the files.
I can do this using vlookup,but my boss wants to run a macro to automate this, as there are several columns to do vlookup.

I have put a example file.

Can experts help me this.

[rylo]

Hi

OK, try this. It does assume that column A is already in existence, but you have to insert new columns G:K

Please Login or Register  to view this content.

rylo

[Deepa12]

Thanks for your reply
Whren i run the macro on the test sheet, it run fine, however when i run the same on the full file, macro shows problem in the below code line

"Cells(i, 7).Resize(1, 5).Value = findit.Offset(0, 6).Resize(1, 5).Value"

kindly help and pls explain what does that mean

[rylo]

Hi

What you are doing is making the values in the output range the same as the values in the source range.

Cells(i,7).resize(1,5).value : this grabs the single cell (cells(i,7)) and then resizes it to be a range 1 row by 5 columns (resize(1,5)) and then says to do something with the value

findit is a range variable that is the position on the data sheet of the required key. It is set in the previous line in the code.

once you have the findit range then you move 6 columns to the right (offset(0,6)) and take the range 1 row by 5 columns (resize(1,5)) and take the value from that range.

Gees, I hope that makes sense.

rylo

[Deepa12]

hi Rylo,

Thanks for taking time out to explain. I am trying to understand that.

However when i run the macro, i am getting the following error

"Run time error 91

Object variable or with block variable not set "

how can i change the macro so that this does not get generated. Further the macro run for "160 rows" populating the correct output and is stopped by the above error.
how do u rectify...i hope u can help me with this

thanks so much again

[Deepa12]

Further, when i run the macro i have inserted the column A alone, and other columns from G to K, get populated automatically, rather than inserting columns G : K as specified in your first reply

[rylo]

Hi

Have you run the code against the example file, assuming that column A is there and columns G:K have to be inserted? Does it work there?

rylo

[jindon]

Different method

[Deepa12]

@ rylo
Yes I have run the code in the sample file...it picks up correct data in it...however macro automatically inserts columns G to K, and i need to insert only Column A...

When i run the macro for larger data, i get the remark of

"Run time error 91

Object variable or with block variable not set "

how can i change the macro so that this does not get generated. Further the macro when run populates output for "160 rows",and is stopped by the above error.
how do u rectify...i hope u can help me with this

[jindon]

1) Add one line

Please Login or Register  to view this content.

2) I don't understand.
Can you upload the file that you got the error?

[Deepa12]

@ jindon Thanks for your reply.The earlier reply no 9, was for rylo
You wanted me to add one line...can you pls tell me its significance?

thanks once again

[jindon]

Here........

[Deepa12]

Here........

@ jindon ...thanks for the reply

however i am unbale to run the macro in the file, not certain where the mistake is

i am re- attaching the file, containing your macro and my file.....

i have specified the details of output in the sheet within the book named " Current month-Result file"

pls help me out

[jindon]

It's an Event driven code, so you don't need to create a standard module.

Use as it is and the code will run every time you select the sheet.

[jindon]

Data in the other thread...

You have 74192100 in Mater data, but 741921001 in Month data.

[Deepa12]

Data in the other thread...

You have 74192100 in Mater data, but 741921001 in Month data.

Thank you so much Jindon..........You are a great Forum Guruu indeed!