[Macro Inquiry] How to find every last Friday with MMdd format in excel macro?

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?

Sub Macro21()

shtName = Format(Now(), “mmdd”)

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)
Selection.AutoFill Destination:=Range(“G5:G74”), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = “=SUM(SUMIF(” & lookUpSheet & “!C6:C7,RC2,” & lookUpSheet & “!C7:C7)+” & lookUpSheet2 & "!RC7)

End Sub

Thank you for helps always :slight_smile:

Could you please have a look for this question…?
I left this question several times but couldn’t get answer for a while.
It would be appreciate if you make comment on this !! if you know. :slight_smile:

Hi @Dorothy_lee

Have you got the friday date logic?
and what if today is not tuesday


1 Like

@prasath_S Thank you for your reply!! Yes, I’ve made logic to printout Friday date with MMdd and Robot is always running on Tues and Friday. So, Always Tues is printed out on every Tuesday:)

@Dorothy_lee so may I know what exactly is the issue?

Thank you for asking :slight_smile:
The problem is I don’t know how to set variable date in Macro sheet(yellow remarked)
With above script, it shows error.
If today is Tuesday and it has to select last Friday sheet and refer last friday sheet’s value with Macro…

It’s resolved with shtName = Format(Now()-4, “mmdd”)


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.