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:

Please Login or Register  to view this content.

Thank you for your guidance in advance.

With regards,
Roshan Shakya

Possibly you could adapt this...

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.