VBA Script to use any open file when copying and pasting
 

I have a VBA script that copys data from a particular excel sheet into a new
workbook.

Currently, if the file name changes, the script does not work. For the code
below, what do I need to enter that ANY file name will work.

Workbooks.Add
Windows("SpecificFile.xls").Activate
Sheets("Assumptions").Select

What would replace Windows("SpecificFile.xls").Activate such that if a file
opened called File1.xls, or File9.xls, etc, woudl work.

Charles

VBA Script to use any open file when copying and pasting
 

Dim myFileName As String
Dim myWorksheet As String
'assume you want to return to current workbook after
' creating new workbook
myFileName = Application.ActiveWorkbook.Name
'assume you want to return to current worksheet after
' creating new workbook
myWorksheet = Application.ActiveSheet.Name

Workbooks.Add

Windows(myFileName).Activate
Sheets(myWorksheet).Select

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Charles" wrote:

I have a VBA script that copys data from a particular excel sheet into a new
workbook.

Currently, if the file name changes, the script does not work. For the code
below, what do I need to enter that ANY file name will work.

Workbooks.Add
Windows("SpecificFile.xls").Activate
Sheets("Assumptions").Select

What would replace Windows("SpecificFile.xls").Activate such that if a file
opened called File1.xls, or File9.xls, etc, woudl work.

Charles