Code to copy data from multiple worksheets

[klunker]

Hi All,
I've put together a code which opens a workbook and copy and pastes data into another workbook. Right now my code only selects a specified sheet to copy from (strName) but what I would like to do is copy data from each sheet in the data workbook. The sheets are named "Array 1, Array 2, Array 3, etc., etc.,. The number of sheets could vary up to 100+. I've copied the code I'm currently using below for reference. Thank you for all your help!

Please Login or Register  to view this content.

[pjwhitfield]

Are these the only sheets in the workbook ie there are no "Sheet1" or "General Data" or "Names of my cats" sheets in there as well as the "Array" sheets?

[klunker]

Hi PJ,
The first sheet in the book is "Project Summary Page". After that they are all titled "Array #".

[pjwhitfield]

Put the following before your "wbTargetBook.Sheets(strName).Select"

Please Login or Register  to view this content.

End the If and close the Loop wherever you think is best (do you save first etc?), you'll also however need to add in a bit to paste following on from the last row used, apologies, Im not near my Excel so cant quite work that bit out.

[xlbiznes]

Hi,

why don't you loop through the sheets of the source like this :

Please Login or Register  to view this content.

[klunker]

Hi PJ,
I input your suggestion as follows but it's not indexing through the sheets. It still works to copy and paste as coded but only on sheet Array 1. Have I ended the if and loop in the wrong locations?

Please Login or Register  to view this content.