Macro to Open Sequential Named Workbooks

[Topcat04]

Hi All
I have a need to open up 52 weeks worth of individual workbooks and perform the same function on each workbook. The workbooks are named GMWK01.xlsx, GMWK02.xlsx etc

Once open, I need to Insert a column between Column A and Column B, then perform a Right(A1,6) - this goes all the way down to A50
In a new Workbook (Book1) that is open I need to perform a vlookup on a entry in Column A, against the newly created RIGHT result in GMWK01 column B and have the result go into column C of Book1. Close down GMWK01.xlsx and then open GMWK02 and perform the same function, but this time putting the result in Column D of Book 1, and so on through to GMWK52, so that each week is represented as a new column in Book1.

If its easier, I can have the results go into C1, C2, C3, C4 ..... C52 of Book 1.

Im hoping this kind of Macro can be created as it will same me so much time.

Appreciate any help.
Thanks
TC

 

[SkipVought]

Hi,

Your problem is a textbook example of why un-normalized data is a curse and ought to be avoided like the plague!

Please explain WHAT you need to accomplish rather than HOW you think it ought to be accomplished. Please do not use Excelese like VLOOKUP or RIGHT or anything that is related to Excel features. Just explain what is needed given the 52 workbooks and why the need exists.

I'd bet that there's a much better approach!

 

[Topcat04]

Hi Skip

My requirement is to consolidate 52 weeks of Sales into one new spreadsheet. There is approx. 50 items that need to have 52 weeks sales recorded against them.
I have a new master sheet with the 50 skus listed ready to have the data pulled in.
The GMWK sheets have the skucode in Column A and have the Sales Data in Column N. The Sku are not listed in the same order in each sheet as it is sales sorted and not sku sorted.
Due to headers and other information, the data starts on Row 15.

Hope that helps better explain.
Thanks
TC

 

[SkipVought]

So you're just interested in the data in columns A & N in these 52 workbooks?

And you're going to spread the colum N data across 52 columns (weeks), corresponding to your SKU data?

 

[Topcat04]

Hi Skip
That's correct, thank you.
TC

 

[SkipVought]

If you're planning to ONLY use this like a paper display, for viewing only, snd this will NEVER be used as a source for data analysis, then it might be okay.

However it is not the path I would take as it is a dead end solution, and I would rather have data that I can use to do useful accounting stuf with, make charts, compare to previous and or current year's performance.

 

[Topcat04]

Thanks Skip
That is the end result, however this is a stop gap just to be able to get the data out of the 52 weekly reports that I have inherited.
cheers,
TC

 

[SkipVought]

Wouldn't you rather have all the data from the 52 sheets in one tabel, from which a very simple PivotTable will generate the report you want but will also make it the base for adding future data. You'll need one more column to designate the Date of the Week.

 

[Topcat04]

Skip
I think that will be a great solution
Thanks sir
TC

 

[SkipVought]

we should be doing this in forum707, as it would attract other coders who can assist as well.

Turn on your macro recorder and record opening ONE workbook, select the data for the entire table, including one row or heading, select the sheet in the target workbook and paste the data at A1.

Turn off the recorder, copy the recorded code and paste it in a new thread in forum707.

Then we'll go from there.