Macro to look at other opened Excel book

I have a macro that does a vlookup on another Excel file that is already open based on the file name. I want to use this macro with Excel files that have different names. Is there any way to update my code below so it works with whatever other open Excel file exists and is not tied specifically to the file named below?

My rational is that if I only have one other file open, it will always go to the correct file, regardless of the file name.

Windows("Payroll Calc MACRO TEMPLATE 4-8-2013.xlsm").Activate

I want it to be something like: Windows("Payroll Calc MACRO TEMPLATE*.xlsm").Activate
Where the * represents various possible naming conventions.


Thanks,
Steve

Thanks - I'm confused about how to add this code to the following vlookup. The file named "Payroll Calc MACRO TEMPLATE 4-8-2013.xlsm" is the variable. It might be named something else. If it helps, I can restrict the naming convention so it always begins with "Payroll Calc."

Windows("Vlookup Sheet.xlsx").Activate
Range("M1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Payroll Calc MACRO TEMPLATE 4-8-2013.xlsm]Template 1'!R13C2:R25C15,1,FALSE)"

Originally Posted by HaHoBe

Hi Steve,

from what I understood (you could use InStr for the workbook name):

Please Login or Register  to view this content.

Ciao,
Holger