Specifying a specific sheet in a "FindString" command

[jbrooks1988]

I have the following VBA code to retrieve data from hundred of spreadsheets. The data is located in sheet 1, named "Sheet1". A new sheet has been added to all the spreadsheets (not relevant to the data this macro retrieves) but each spreadsheet has been saved whilst the new sheet is active, so the command is looking in this sheet for the data "Total Purchased" rather than Sheet1 which was always the default active sheet. How can I amend my code to look in the correct sheet?

I hope this is clear! Thank you

Please Login or Register  to view this content.

[walruseggman]

Something like this:

Please Login or Register  to view this content.

and then all your Ranges refering to that sheet inside that with statement need to look like:

Please Login or Register  to view this content.

etc. etc.

[jbrooks1988]

Hi, I dont understand? Do I just add the line "Set ws1 = wb1.Sheets("Total Purchased")" into the macro after "wb1.Activate"??

Apologies, I didnt write this code and am a complete VBA novice..