Macro to vlookup across multiple columns

Hi,

I'm looking for a macro code that could automatically vlookup from sheet1 to sheet2 across many weeks.

This is the code that I have but it only works for one column, I need it to loop to the next column starting from column D. Because this will be updated every week, next week sheet 1 will add one more week data, so If you could figure out a way that when the next week column has data it will vlookup from week 1 to the latest week. Appreciate if you can help.

Please Login or Register  to view this content.

I forgot my attachment.

Macro Help.xlsm

This is just an example since I have to update my mastersheet from another sheet, If I can figure out the code to do this I'd be able to apply to my real situation.

All I need is to vloopup from sheet1 then copy and Paste values in sheet2 to get rid of formulas.

The problem is I could not figure out how to implement the macro code for different column index number in the Vlookup function to be changing when it moved to a new column.

We can't do this with macro, can we?

I had the formula already, it just everyweek I have to do this with large volumn of data and just think an alternative way to make it easier via vba.

Thank you for your inputs, I really appreaciate it!