How to select every last Friday date with MMdd format?

Dorothy_lee

1d

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:

Sure. The VBA code for last Friday as MMDD is: Format(Now - (Weekday(Now(), vbFriday) - 1), “mmdd”)