File Names

M

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 ***
 
E

Earl Kiosterud

Michael,

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

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 **
 
E

Earl Kiosterud

Michael,

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

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

Similar Threads

Two Excel forms- swapping data. 1
excel vba 1
User Defined Function 2
how to embed macro into new/create spreadsheet from outside of EXCEL? 1
Sum cells flagged by other cells 2
Pivot Table 1
How do I launch MS-DOS prompt to execute a batch file from within Excel worksheet with /VBA? 11
Running a macro automatically 15

Top