Paste sheet to new file with unknown file name - VBA
 

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 Befo=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

Paste sheet to new file with unknown file name - VBA
 

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("She et3") _
.copy after:=bk.sheets(1)
activesheet.Name = "Custom Sort"
exit for
end if
end if
Next

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
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 Befo=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

Paste sheet to new file with unknown file name - VBA
 

Tom-

Works great! Thanks again for all your help.

-Chris