Copy Data from Same Cell in Multiple Sheets to One Column on Summary Sheet

Hey y'all!

I use a software that automatically generates spreadsheets using a fixed template. I use a separate macro to merge the individual sheets into a master workbook, which already has a summary sheet in it. The summary sheet has indirect formulas that reference data I manually input on the same sheet in order to locate the appropriate sheet to pull from. I'm trying to prevent having to enter any data manually, because it wastes a lot of time when I know VBA can do it. I've included the merge & sheet rename macro code in the workbook & feel free to copy it for personal use if you want it!

I'm having issues getting the macro to loop through the worksheets and copy the cell value from each sheet, and add them into a column in order of the sheet's index number, stopping when there are no more sheets.

Basically, I need Merged1!B2 to be copied to Summary!B2, Merged2!B2 to be copied to Summary!B3, and so on and so forth. I'm pretty sure using R1C1 form is going to be easier for the macro, I'm just not as familiar with it as A1. I can't use a 3D Dynamic formula, because I don't know the sheet names or how many there are until I've merged the book together. I have a static "start" sheet, but no "end".

Please Login or Register  to view this content.

Where SummarySheet is the sheet I need the data copied to, which is first in the workbook and already there when the sheets are merged into it. ws is the worksheet I need the data from, and B2 is the data location that's the same across all the sheets (except the Summary sheet).