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

Hi,

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. I am not the owner of that workbook and I open ReadOnly while accessing. The first 2 characters in all other worksheets do not have numeric values.

The access to this workbook is automated and I cannot hardcode the date because of variation of month-end name. My macro stores day, month, and year in separate variables. I am seeking your guidance to solve if for instance, the name of the worksheet is "28-10-2020", how can I change the value to 28 in day variable using loop and if condition?

I activate the worksheet that I access using this statement:

Worksheets(ld & "-" & pm & "-" & cy).Activate

Thank you for your guidance in advance.

With regards,
Roshan Shakya

Possibly you could adapt this...

Sub Last_Business_EOMonth()
    Dim d As Date
    
    d = DateSerial(Year(Date), Month(Date) + 1, 0)
    Select Case Weekday(d, vbSaturday)
        Case 1
            d = d - 1
        Case 2
            d = d - 2
    End Select
    
    MsgBox Format(d, "dddd mmm d, yyyy")
End Sub

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