New to Macros - file naming issue

[ngann]

So, I am pretty new to creating Macros and I would imagine this is a pretty simple problem. The code works great if I keep the file name the same as the spreadsheet I created it in. However, the code I am using will not work once I change the file name. I looked at a forum and tried to correct it myself but it just made the code unusable. Below is the code



Sub SE()
'
' SE Macro
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Dim sSheetName As String
Dim sDataRange As String

sSheetName = ActiveSheet.Name
sDataRange = Selection.Address

Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RFP!R[2]C3"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RFP!R[2]C4"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RFP!R[2]C5"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RFP!R[2]C6"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
Selection.NumberFormat = "General"
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RFP!R[2]C8"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,13,FALSE)"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RFP!R[2]C7"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,7,FALSE)"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,6,FALSE)"
Range("L2").Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,2,FALSE)"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,4,FALSE)"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,11,FALSE)"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Pulled History'!R1:R1048576,12,FALSE)"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=RC23-RC21"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Range(sDataRange).Select
ActiveCell.FormulaR1C1 = "=IF(RC9>0,""History"","""")"
Range(sDataRange).Select
Selection.AutoFill Destination:=Range(sDataRange)
Range(sDataRange).Select
Range(sDataRange).Select
Sheets("Summary").Select
Range(sDataRange).Select
ActiveSheet.PivotTables("Pricing Summary").PivotCache.Refresh
End Sub

[Logit]

What is the filename now ?

[ngann]

The filename was SE Template but it will be always changing

[mdmackillop]

An issue with recorded macros is the verbiage. Can you post your workbook Go Advanced/Manage Attachments

[Logit]

I don't see SE Template mentioned in the macro you've posted. There must be more code ? As suggested post your workbook for review.

[SamT]

Does Range(sDataRange).Select ever change Cells? After all, the sDataRange address is basically a fixed value. it is never changed.

I'm probably wrong, but it looks to me as if almost the entire procedure can be rewritten as

With selection
.Formula = This
.Formula = That
.Formula = The other
.Formula = This
.Formula = That
.Formula = The other
.Formula = This
.Formula = That
.Formula = The other
.Formula = This
.Formula = That
.Formula = The other
.Formula = This
.Formula = That
.Formula = The other
End with


Because Range(sDataRange).AutoFill Destination:=Range(sDataRange) does nothing at all