[Macro Inquiry] How to set last Friday date format in excel?

Hello, all

I’m struggling to set Macro for finding Last Fri “mmdd” format sheet name.
My Logic is if today is Tuesday(Main sheet is “0511”), it has to refer Last Friday sheet(Name:0507)
Can I set with Macro script?
image


[txt]
Sub Macro21()

shtName = Format(Now(), “mmdd”)
Sheets(shtName).select
Range(“G5”).Select

lookUpSheet = “HANA” & shtName

shtName2 = Format(Now.AddDays(((DayOfWeek.Monday) - (DateTime.Now.DayOfWeek)) - 3), “mmdd”)
lookUpSheet2 = shtName2

ActiveCell.FormulaR1C1 = “=SUM(SUMIF(” & lookUpSheet & “!C6:C7,RC2,” & lookUpSheet & “!C7:C7)+” & lookUpSheet2 & "!RC7)
Range(“G5”).Select
Selection.AutoFill Destination:=Range(“G5:G74”), Type:=xlFillDefault
Range(“G5:G74”).Select
ActiveCell.FormulaR1C1 = “=SUM(SUMIF(” & lookUpSheet & “!C6:C7,RC2,” & lookUpSheet & “!C7:C7)+” & lookUpSheet2 & "!RC7)

End Sub

Thank you for helps always :slight_smile: