Variable file name in copy macro

[wilson4j]

I need a solution for this code where Workbooks("newitemform.xls").Sheets(2),
the file name of the active workbook can be different. Is there a way so that
instead of this fixed file name, the active workbook name could be pulled in
here?

The file name could be names of product and not the standard "newitemform"
name.

Private Sub CommandButton8_Click()
'
' getpattern01 Macro
' Macro recorded 1/9/2008 by 7-Eleven, Inc.
'
'
ChDir "C:\data\newitemsetup"
Workbooks.Open Filename:="C:\data\newitemsetup\Pattern_Sheets.XLS", _
UpdateLinks:=0
Sheets("aptrn01").Select
Sheets("aptrn01").Copy After:=Workbooks("newitemform.xls").Sheets(2)
ActiveWindow.ActivateNext
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True


End Sub

 

[Otto Moehrbach]

The designation for the workbook that holds the code that is running is
"ThisWorkbook". You can use that instead of "Workbooks("TheName.xls").
If you want to refer to the active workbook, but not by name, use
"ActiveWorkbook".
Note that both of these are variables and you should remove the quote marks.
HTH Otto

 

[Bob Phillips]

You said it yourself

Sheets("aptrn01").Copy After:=ActiveWorkbook.Sheets(2)


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[wilson4j]

Well my question was not detailed enough. I start with a shell doument called
"NewItemForm.XLS". A user will take this document and enter data and save it
to a different name (let's call is Widget1.xls). The document is then passed
onto anoth user who execute the code by pressing a button to copy information
from another workbook (in this instance the button has code to copy sheet
aptrn01 from Pattern_Sheets.XLS). The code right now has a static file name
of NewItemForm.xls. I changed it to use Copy After:=ActiveWorkbook.Sheets(2)
and that code placed a copy of Aptrn01 in Pattern_Sheets.XLS not in the
Widget1.xls file.

So the code is in the shell file NewItemForm.xls which gets renamed by the
first user. then when the second user trys to execute the copy function, the
code references the static file name and needs to reference the new name. If
the user has to answer a variable prompt for which file the copy should be
placed in, that would work too.

thanks for all your help.

 

[Otto Moehrbach]

Just say ThisWorkbook or ActiveWorkbook. Otto

Well my question was not detailed enough. I start with a shell doument
called
"NewItemForm.XLS". A user will take this document and enter data and save
it
to a different name (let's call is Widget1.xls). The document is then
passed
onto anoth user who execute the code by pressing a button to copy
information
from another workbook (in this instance the button has code to copy sheet
aptrn01 from Pattern_Sheets.XLS). The code right now has a static file
name
of NewItemForm.xls. I changed it to use Copy
After:=ActiveWorkbook.Sheets(2)
and that code placed a copy of Aptrn01 in Pattern_Sheets.XLS not in the
Widget1.xls file.

So the code is in the shell file NewItemForm.xls which gets renamed by the
first user. then when the second user trys to execute the copy function,
the
code references the static file name and needs to reference the new name.
If
the user has to answer a variable prompt for which file the copy should be
placed in, that would work too.

thanks for all your help.