Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Filename in Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Filename in Macro
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 -- Gary''s Student - gsnu200826 "Havenstar" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Filename in Macro
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200826 "Havenstar" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change Filename in Macro
Hi,
I have figured out i changed "Windows("XX-TEST-0301.xls")" to "ThisWorkbook" Thanks Havenstar "Havenstar" wrote: 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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200826 "Havenstar" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro reference changes with filename | Excel Discussion (Misc queries) | |||
Excel macro to prompt for filename | Excel Discussion (Misc queries) | |||
filename in macro | Excel Discussion (Misc queries) | |||
Put Filename Variable in Macro | Excel Discussion (Misc queries) | |||
Recorded macro: What happens if I change filename? | Excel Discussion (Misc queries) |