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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com