accessing worksheet with name that varies with last business day of the month

Originally Posted by Roshan.Shakya

I access a workbook that has number of worksheets and one worksheet name contains only month-end date (for instance "31-10-2020")? However, when month-end is a public holiday and/or weekends, the name of worksheet would be the last business day.

Assuming you have list of holidays in sheet named "Holidays" from A2:Ax in Date format.

Sub test()
    MsgBox Format$(EOMWorkDay(Date), "dd-m-yyyy")
End Sub

Function EOMWorkDay(myDay As Date) As Date
    With WorksheetFunction
        EOMWorkDay = .WorkDay(.EoMonth(myDay, -1) + 1, -1, Sheets("holidays").Range("a2:a1000"))  '<-- expand 1000 to suite, if needed.
    End With
End Function