Copy multiple values from multiple worksheets from multiple workbooks - with a twist

Hi all,

I was recommended this site to help with a problem I have, and after searching the site, i've found some good help/solutions for some of my problems - but this one specific is a bit unique.

Problem:

• I have multiple workbooks (that each contain multiple worksheets) that I need to extract values from, and compile them into their own worksheet.
• All the worksheets within each workbook are standardized names (ie. workbook 1 has 5 worksheets named "bill", "day (1)", "day (2)", "final", etc, workbook 2 has 7 worksheets named "bill", "day (1)", "day (2)", "day (3), "final", etc).
• The values will be located in the same cell across the same worksheet name across each workbook (ie. Cell C3 from "day (1)" across all workbooks").
• Will need to gather multiple values across multiple worksheets within each workbook, and paste it into its own worksheet (ie. Copy Cell C3 from "day (1)", copy cell G12 from "bill", copy cell B5 from "final" > compile into it's own worksheet, so it goes C3 in A1, G12 in B1, B5 in C1, etc).
• This will need to be done across multiple workbooks.

I currently have a macro that compiles data across multiple workbooks, but only from one field, and from one worksheet, into a single workbook.

I will keep looking through the forums for a similar question/answer, but any help with this would be appreciated! Let me know if anything needs to be clarified.

Thanks,
BK

Yes, I can put all the main workbooks in the same folder. Each workbook is
fairly identical (work sheet names, cell value locations, etc).

Thanks!

Thanks!

I've uploaded a typical sample workbook, that the data would need to be extracted from. Each workbook wouldn't vary much from this; and all values will be located in the same cells across each workbook.

I also uploaded a sample "master" worksheet that would be generated for each workbook, if that helps clarify anything.

Thanks again,
BK

Sorry, Let me try to explain a little better. The two worksheets in "Sample worksheet to be generated.xlsx" that i uploaded in my last post contain values (the #REF!) that need to be populated from each workbook ("sample workbook.xlsx"). For simplicity's sake, the sample workbook has the values listed just as "job number" or "client" in their respected cell.

So ideally, the "sample worksheet to be generated.xlsx" would have under Job Number (in A2) the value "Job number value", and so on. The blank columns (ie "Field" will need to be filled in manually afterwards, which is fine.

The source workbooks (represented by "sample workbook.xlsx") are all similar in the fact that they have the same work sheet names, and the values needed to be extracted are located in the same cell locations in each worksheet within each workbook.

So there's a few hundred workbooks, all with the same worksheet names within, with values in specific cells (that locations stay the same across each workbook (for example, Job Number will always be found in each workbook under the "Bill" worksheet, in cell E1)

What i'm looking to do, for each workbook, is to extract values from various cells, into their own worksheets (as represented by "sample worksheet to be generated.xlsx"). For each workbook, a new workbook would be created with the two worksheets in them (12CJ-000 MAIN, 12CJ-000 BHA) with all of the specified values pulled in from the source workbook.

Then loop again for each source workbook in the folder (ie, if there's 100 workbooks in the folder, 100 workbooks would be generated with two worksheets containing all copied values. "Sample workbook_1.xlsx" would generate "Sample worksheet to be generated_1.xlsx", and so on.

Hope that helps!

Thanks so much xladept, I'm having a little trouble getting it to work (script out of range on "Set ww = Workbooks (S)") - all the source workbooks are located in the same folder.

The current macro I have now works, but only has the capability to extract one cell of data per workbook, and is compiled into a single workbook in the end. Here's the code just incase it helps. Thanks again!

Please Login or Register  to view this content.

Thanks for the response - the macro currently compiles and does create the generated workbooks, which is great!
It seems like it's having a bit of trouble extracting some of the data though. After a few test runs, it seems to not pull in any data into the "12CJ-000 MAIN" worksheet, and only the first row (and only a few values) into the "12CJ-000 BHA" worksheet.

Here's the current code i'm using:

Please Login or Register  to view this content.

Thanks

Here's one of the generated reports:

All the sheet names are identical, so i'm not sure why it would not catch the sheet names.

All the sample workbooks would look like the first sample workbook i posted, but have different values provided, and more information in other cells that aren't of any use.

I'll give this a shot - Thanks so much for all your help! Much appreciated!