VBA Script to use any open file when copying and pasting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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.
 
Back
Top