Opening Excel Page Based On Month

[DMT Dave]

Hi Guy's hope everyone is well,



I have an Excel file with 12 Months names as follows

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

These pages never change name so i am hoping i can format a Month (now() so today, it would open on page Mar, tomorrow it would open on page Apr ?

Kindest

[DMT Dave]

Hi Guy's got it sorted as below

[Welshgasman]

It would.
Have you tried the macro recorder to select a worksheet, then modify that for your required date?

[DMT Dave]

'Dim calPath As String, calFile As String

calPath = "T:\XL Files\"
calFile = "2021 Holiday Calendar.xlsx"


Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim myMonth As Date
myMonth = Format(Now(), "mm")


Set xlApp = New Excel.Application


xlApp.Visible = True


Set xlWkbk = xlApp.Workbooks.Open(calPath & calFile)


xlApp.Sheets(myMonth).Select


Set xlApp = Nothing
Set xlWkbk = Nothing
Set xlSht = Nothing

[DMT Dave]

Hi welshgasman, thank you for your reply, the code i have pasted does the trick but much appreciate your response

[Welshgasman]

I *think* that is selecting the sheet by the index?
If you move the sheets around, perhaps so that the current month is first then that would not work?

I think you'd be safer with 'mmm' format? according to your naming convention.?

Edit: It does, just tested it.