ngann
07-31-2017, 01:10 PM
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
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