Copy from Workbook to Workbook - BUT I don't have name of 2nd book

[ker9]

Hi, is it possible to copy data from one workbook to another if you don't know the name of the 2nd workbook?
Scenario:
Wb1 - New workbook has vba code to copy from Old Wb2, but I do not have name of old book. I would have both open at the time.

I don't know the name because it resides inside a third-party application which defines the name. Also, this would be used on multiple different files with different names, but the guts of the workbook are the same. (Clear as mud?)

Anyway, bottom line is workbook to workbook, without a name. Somehow I would have to ensure that only those 2 workbooks are open. The Old workbook would have a named range that is the same as the new workbook.

Thank you.

[JasperD]

Hello Ker9,

use the following code to determine the second workbook - which will be set as '"wb'' - then you can use code to copy between '"wb.Sheets(x).Range...'" etc and '"ThisWorkbook.Sheets(x).Range...'" etc.

Please Login or Register  to view this content.

Please click the * below if this helps

[ker9]

Hi, it's a good start, the problem is with hidden workbooks.

Please Login or Register  to view this content.

I have two hidden (Personal.xls and Personal.xlsb) and so might my users. The users might also be totally unaware that there are hidden workbooks open.

I just found some great code that lets me capture the names of all the open workbooks:

Please Login or Register  to view this content.

But I haven't yet figured out what to do with it.
I might be able to ask them which one to use. Unfortunately, they may not know since it will have an alphanumeric naming scheme that makes no sense.