File Names

[Michael Hayes]

I've created a Macro to copy data from a "data file" to an "input file".
If I get a new set of data and and save my data file as "data file2" my
macro points to the original data file. How can I make the macro
automatically point to the newly saved file as opposed to the original
file.

TIA

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Earl Kiosterud]

Michael,

Post your macro. You can select and copy it and paste it into your reply.

 

[Michael Hayes]

Below is the macro.....The current file is called "Test Template". If I
save the file as "Test Template 2"....I want the macro in the Test
Template II to change to refelect the new file name.


ChDir "U:\Non-CA PPO Pricing Models"
Workbooks.Open Filename:="U:\Non-CA PPO Pricing Models\nonca 2004
v2.xls"
Windows("Test Template.XLT").Activate
Range("B3:C3").Select
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B3:C3").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("B4:B6").Select
Range("B6").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("E3").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("E5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("E5").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("E7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("E7").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("B11:B13").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B11").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("E10").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("B17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B17").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("B19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B19").Select
ActiveSheet.Paste
Range("B29").Select
Windows("Test Template.XLT").Activate
ActiveWindow.SmallScroll Down:=14
Range("B25:E27").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B25").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=12
Range("C38").Select
Windows("Test Template.XLT").Activate
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=8
Range("B36").Select
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B36").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("B38").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B38").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=6
Windows("Test Template.XLT").Activate
Range("B41:B43").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B41").Select
ActiveSheet.Paste
Windows("Test Template.XLT").Activate
Range("B45").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("B45").Select
ActiveSheet.Paste
Range("D46").Select
Windows("Test Template.XLT").Activate
Range("E36:E38").Select
Application.CutCopyMode = False
Selection.Copy
Windows("nonca 2004 v2.xls").Activate
Range("E36").Select
ActiveSheet.Paste
Range("D47").Select
Windows("Test Template.XLT").Activate
End Sub


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Earl Kiosterud]

Michael,

Instead of
Windows("Test Template.XLT").Activate
try
ThisWorkbook.Activate

 

[Michael Hayes]

Thank You!!!!!!!!!!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Michael Hayes]

Thank You!!!!!!!!!!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **