File Reference

[nick]

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]

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.