Getting Vlookup to work via macro in multiple spreadsheets

[HoShe]

I'm very (VERY!) new to writing Macros, so I think I'm biting off more than I can chew...

We have about 190 locations that we pull files from. I'm trying to pull these files and do a Vlookup to a tax table available on our Intranet. The problem I'm having is that when I "write" the macro (using the record button), I can get it to work but the code is showing the specific name of the spreadsheet I'm writing from. I want to be able to run the macro to do a Vlookup on any of the 190 locations we pull, which would all be named differntly.

My current line shows this:
Windows("LOC401 PUMPKIN CUSTOMERS NOV 2014.xls").Activate

So, my macro only works in this spreadsheet. Can I make this more generic so it will work with any spreadsheet?

Thank you,
HS

 

[SkipVought]

Hi,

Wow! 190 different workbooks!

You must have a list of the Path/file Name for your 190 workbooks.

Then first task, loop thru the list and build ONE TABLE containing data from your 190 workbooks.

Then do yup VLOOKUP(). You might not even need a macro to do the VLOOKUP() if you have ONE TABLE.

 

[HoShe]

Thanks, SkipVought. I was afraid it wasn't as easy as I was trying to make it. My next goal is to get IT to great a global report so that instead of running the report 190x, I can run it once and get all the data in one file. I appreciate your time and answer.

 

[SkipVought]

That's why I stated the first task. If IT can do that, then great.

Otherwise it is very possible for you to write a procedure to do that in your workbook. You can get help here to aid you in writing such a procedure.

 

[Andrzejek]

I was afraid it wasn't as easy as I was trying to make it"
Actually it will be very easy once you gather all of the information in one place. Which is not that difficult if those 190 workbooks have the same structure (one can hope )

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[HoShe]

Happy Friday...I'm tagging on to my earlier question, as I'm a little further along with this process.

I'm running my Macro from a active spreadsheet (Macro1.xlsm) and I have manually copied my tax table into the second tab, named ("Tax Table"). I need to write the code to copy the tax table over from MACRO1.xlsm to my other active spreadsheet, ICSR.xls, to the tab named "Tax Table". The range I need to copy is A1:F3000 and I just need to paste to A1 in the destination workbook.

Can anyone assist with this code?

 

[SkipVought]

Turn on your Macro Recorder and record the process of copying the Tax Table, selecting the workbook/sheet/range and pasting into that sheet.

Then turn off the recorder, copy the recorded code and paste it here for help in customizing.