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