Change Filename in Macro

  • Thread starter Thread starter Havenstar
  • Start date Start date
H

Havenstar

Hi,

I have the following Macro that I have a question on. The filename
("XX-TEST-0301.xls") will always be a different name, how can I update the
Macro to work when the filename changes?

Workbooks.Open Filename:= _
"J:\Blank Templates\Sales Process Templates\WIP\Work Order -
Upload\Estimate Spreadsheet-NEW Test 1.xls"
Sheets("Estimate Template").Select
Sheets("Estimate Template").Copy After:=Sheets(1)
Windows("XX-TEST-0301.xls").Activate
Range("A1:F30").Select
Selection.Copy
Windows("Estimate Spreadsheet-NEW Test 1.xls").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

Thank you,
Havenstar
 
One way is to allow the user to pick the name:

Sub dural()
Dim s As String
s = Application.InputBox(prompt:="enter file name", Type:=2)
Windows(s).Activate
End Sub
 
Hi,

Not sure if that is what I would like to accomplish. Actually, the filename
that will be changing is the actual Workbook with the Macro Button on. Does
that make a difference?

Thanks.
Havenstar
 
Hi,

I have figured out i changed "Windows("XX-TEST-0301.xls")" to "ThisWorkbook"

Thanks
Havenstar
 
Back
Top