VBA Previous Monday Sheet Code

Question

All the coding below does what I want except where it references sheet 2-28-22. I would like it to reference whatever the previous Monday's date was since these sheets are made on a weekly basis and a couple pieces of data need to be carried over to the new sheet that is created. Thanks in advance!

Sub SheetCreation()

'

' SheetCreation Macro

'

Sheets("template").Select 

Sheets("template").Copy Before:=Sheets(14) 

Sheets("template (2)").Move Before:=Sheets(2) 

Dim dNext\_Monday As Date 

dNext\_Monday = DateAdd("d", -Weekday(Now) + 9, Now) 

MsgBox "If today's date is '" & Format(Now, "DD MMM YY") & "' then" & vbCrLf & \_ 

" Next Monday Date is : " & Format(dNext\_Monday, "DD MMM YY"), vbInformation, "Next Monday Date" 

Sheets("template (2)").Name = Format(dNext\_Monday, "MM-DD-YY") 

ActiveWindow.SmallScroll Down:=-27 

Range("B3").Select 

ActiveCell.FormulaR1C1 = "='2-28-22'!RC[12]+3" 

Range("B7:D7").Select 

ActiveCell.FormulaR1C1 = "='2-28-22'!RC[12]" 

End Sub

Answer 1

Try this:

Sub SheetCreation()
    Dim dNext_Monday As Date
    Dim sLastMonday As String
    Sheets("template").Copy Before:=Sheets(2)
    dNext_Monday = DateAdd("d", -Weekday(Now) + 9, Now)
    MsgBox "If today's date is '" & Format(Now, "DD MMM YY") & "' then" & vbCrLf & _
    " Next Monday Date is : " & Format(dNext_Monday, "DD MMM YY"), vbInformation, "Next Monday Date"
    sLastMonday = Format(dNext_Monday - 7, "MM-DD-YY")
    Sheets(2).Name = Format(dNext_Monday, "MM-DD-YY")
    Sheets(2).Range("B3").FormulaR1C1 = "='" & sLastMonday & "'!RC[12]+3"
    Sheets(2).Range("B7:D7").FormulaR1C1 = "='" & sLastMonday & "'!RC[12]"
End Sub

Remark: there is an inconsistency: 2-28-22 is formatted as M-DD-YY, but you name the new sheet using MM-DD-YY. The code above uses MM-DD-YY.