Find sheet name in another workbook based on name from one workbook

Hi all,
I have to workbooks, one for our data and one from our source. Both workbooks have sheets that are numerically named 1 through 58 and other sheets that don't really matter to me. What I need to do is: If our data workbook is on sheet "1", I want to activate the source workbook and find sheet "1". And then do that from 1 to 58. I need this done by name though and not sheet sequence because sheet "1" is actually located on Sheet4. Does this make sense?

Hi Mordred

Here's one way to do as you described

Please Login or Register  to view this content.

The code assumes both workbooks are in the same folder, that Data is open and that Source is closed. The code opens Source then closes it without saving.

The code will reside in a general module in Data.

Hope this helps.

John

Hi John,
Thank you for that. I think it needs to be tweaked a bit but you've got me going in the right direction.
On the second iteration of this code

Please Login or Register  to view this content.

a Run time error "Subscript out of range" is thrown but I might be able to correct it (at least, I'll try). I appreciate your insight.

Regards,

Hi Mordred

You'll get this message

"Subscript out of range"

if the sheet name doesn't exist. You'll need to check the sheet names to make certain they are named the same.


EDIT Just tried this and it didn't work

You might try changing these two lines of code

Please Login or Register  to view this content.

in the event your sheet names have leading blanks.

John

Hi John,
I realized that while trying to figure it out. I put the code into a mock copy of my original but I only have one sheet name numerically. Your suggestion works perfectly and I thank you for your time.

Cheers