Paste sheet to new file with unknown file name - VBA

[cfatz1]

Hi-

I currently have a macro that copies "Sheet3" from one file and pastes
it into another file. This works fine right now....because I know the
exact file name. The problem I am forseeing is when I have a file open
and the name is different. Here is my current code:

Windows("Macro_Off_Premise.xls").Activate
Sheets("Sheet3").Select
Sheets("Sheet3").Copy Before:=Workbooks("Off Premise
New.xls").Sheets(1)
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Custom Sort"
Sheets("excel").Select
Range("A1:A3").Select

There will always be two files open. The "Macro_Off_Premise"
file....and another file with an unknown name. How do I copy the sheet
from the macro file to the OTHER OPEN WORKBOOK????

TIA!

-Chris

 

[Tom Ogilvy]

for each bk in application.workbooks
if bk.Windows(1).visible = True then
if lcase(bk.name) <> "macro_off_premise.xls" then
workbooks("Macro_Off_Premise.xls").worksheets("Sheet3") _
.copy after:=bk.sheets(1)
activesheet.Name = "Custom Sort"
exit for
end if
end if
Next

 

[cfatz1]

Tom-

Works great! Thanks again for all your help.

-Chris