VBA Script to use any open file when copying and pasting

[Guest]

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

 

[Guest]

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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Guest]

Thank you for the quick resopnse.

Gary, I am running into an error when I enter the code. What I should have
mentioned is that I have 4 tabs on in the .xls file itself, with each tab
being selected at one point in the script to be copied over to the new
workbook.

Please advise.

Charles

 

[Guest]

What's the error? And what is the code that's causing it?
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.

 

[Guest]

Gary, I am receiving :

"Run Time Error 9: Subscript Out of Range"