Hello,
I am fully aware of how ugly this code is going to be as it's a recorded macro and I am by no means a master. I figure my question seems to be a very simple one, but I can't seem to find an answer for it. Basically I am attempting to get my macro to reference a new date in a file name (I/E: Report 02-04-2020 will be Report 02-05-2020 tomorrow). As of right now, the macro will only reference the exact date that is manually entered. (basically I go in every day and change it to the previous day's date). I have tried using *'s/?'s/*.*'s in place of the date in hopes it would work to no avail. I feel like this is a really simple resolution but I can't seem to find it. Here's my code line 11 being the issue I'm trying to resolve:
Code
Sub WorksheetFormulas()
'
' WorksheetFormulas Macro
'
' Keyboard Shortcut: Ctrl+j
'
Selection.AutoFilter
Windows("SPH Shifts Scheduled Formulas.xlsx").Activate
Range("A16:H17").Select
Selection.Copy
Windows("Shifts Scheduled *.* - Final.xlsx").Activate
Range("R1").Select
ActiveSheet.Paste
Range("R2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-17],'[SPH Shifts Scheduled Formulas.xlsx]Agents'!R1C1:R150C2,2,FALSE)"
Range("S2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],'SPH Data'!R1C1:R200C8,8,FALSE)),"""",VLOOKUP(RC[-1],'SPH Data'!R1C1:R200C8,8,FALSE))"
Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-2],'SPH Data'!R2C1:R200C8,4,FALSE)),"""",VLOOKUP(RC[-2],'SPH Data'!R2C1:R200C8,4,FALSE))"
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-3],'SPH Data'!R2C1:R200C8,5,FALSE)),"""",VLOOKUP(RC[-3],'SPH Data'!R2C1:R200C8,5,FALSE))"
Range("V2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-4],'SPH Data'!R2C1:R200C8,6,FALSE)),"""",VLOOKUP(RC[-4],'SPH Data'!R2C1:R200C8,6,FALSE))"
Range("W2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-5],'SPH Data'!R2C1:R200C8,7,FALSE)),"""",VLOOKUP(RC[-5],'SPH Data'!R2C1:R200C8,7,FALSE))"
Range("X2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR((RC[-4]*0.5)+(RC[-3]*0.25)+(RC[-2]*2)+(RC[-1]*0.5)),"""",(RC[-4]*0.5)+(RC[-3]*0.25)+(RC[-2]*2)+(RC[-1]*0.5))"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/RC[-13]),"""",ROUND(RC[-1]/RC[-13],2))"
Range("R2:Y2").Select
Selection.AutoFill Destination:=Range("R2:Y65")
Range("R2:Y65").Select
Range("P1").Select
Selection.AutoFilter
Range("A1").Select
End Sub
Display More