Change Filename in Macro

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
 
G

Gary''s Student

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
 
H

Havenstar

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
 
H

Havenstar

Hi,

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

Thanks
Havenstar
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top