Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Reference
I want to run this macro for different files. The problem is in the macro it
references the specific file I recorded it in. Is there a way to use this macro for differnt files without actually going into the code each time? The file name that will vary is "DRPT BCA Model 2009002.xls" Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/15/2008 by HDR ' ' Windows("REF BCA - Final Model Template revised emissions.xls").Activate Range("C12:AJ15").Select Selection.Copy Windows("DRPT BCA Model 2009002.xls").Activate Range("C12").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Raw Default Values").Select ActiveWindow.SmallScroll Down:=-13 Range("E30").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "0.05" Range("E31").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Reference
If you activate the worksheet that gets pasted before you start the macro, then
you could do something like: Option Explicit Sub Macro1A() Dim RngToCopy As Range Dim DestCell As Range Set RngToCopy = Workbooks("REF BCA - Final Model " _ & "Template revised emissions.xls") _ .Worksheets("somesheetnamehere").Range("C12:AJ15") Set DestCell = ActiveSheet.Range("c12") RngToCopy.Copy DestCell.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False Worksheets("Raw Default Values").Range("E30").Value = 0.05 End Sub You'll have to change somesheetnamehere to the sheet that holds the range that gets copied. nick wrote: I want to run this macro for different files. The problem is in the macro it references the specific file I recorded it in. Is there a way to use this macro for differnt files without actually going into the code each time? The file name that will vary is "DRPT BCA Model 2009002.xls" Sub Macro1() ' ' Macro1 Macro ' Macro recorded 10/15/2008 by HDR ' ' Windows("REF BCA - Final Model Template revised emissions.xls").Activate Range("C12:AJ15").Select Selection.Copy Windows("DRPT BCA Model 2009002.xls").Activate Range("C12").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Raw Default Values").Select ActiveWindow.SmallScroll Down:=-13 Range("E30").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "0.05" Range("E31").Select End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking to another file using file reference typed cell | Excel Worksheet Functions | |||
cross-reference data from one file to another file | Excel Discussion (Misc queries) | |||
File reference | Excel Worksheet Functions | |||
How do I reference external data from a file, file name found in . | Excel Discussion (Misc queries) | |||
copy/paste from one file to another without file name reference | Excel Discussion (Misc queries) |