Code to open worksheet based on date

[Purple Turtle]

I have a workbook that when opened a userform appears. The user inputs data, then clicks a submit button which enters the data , saves and closes the workbook. There are 13 sheets. 12 of them are labeled by months of the year. January, February, March, etc. I need help with a macro that would have the workbook open to the corresponding sheet based on the pc system date. Its August now and everytime the book is opened it will open to sheet labeled August. On September 1, the book will open to sheet labeled September. The 13th sheet is for admin purposes and would not be affected.

 

[JLGWhiz]

Would this not work?

Private Sub Workbook_Open()
Sheets(Format(Date, "mmmm")).Activate
End Sub

Goes in the ThisWorkbook code module

 

[Purple Turtle]

Would this not work?

Private Sub Workbook_Open()
Sheets(Format(Date, "mmmm")).Activate
End Sub

Goes in the ThisWorkbook code module

Thanks. I'll try tomorrow at work. I'll reply if it works. How could I test it sooner than waiting until next month? I'm a noob. How does excel know to do this? What is "mmmm" and could the sheet name be the abbreviated name like Sept or Oct?

 

[JLGWhiz]

Install it in the ThisWorkbook code module, activate the january worksheet, save the file so the code won't be lost when you close the workbook. Then close and reopen the workbook. If it opens on the August worksheet then the code works. It will open on whatever the current month is, but make sure your sheet names are the full name for each month, no abbreviations.

Using the Format function to to get the month name requires that each sheet use the same naming convention for the month. If you do use abbreviations, the 'mmmm' would need to be changed to 'mmm' and the standard three character abbreviation would have to be used on the sheet name tab. But the OP indicated that you are using the full month name so that is how the macro is written.

 

[Purple Turtle]

Install it in the ThisWorkbook code module, activate the january worksheet, save the file so the code won't be lost when you close the workbook. Then close and reopen the workbook. If it opens on the August worksheet then the code works. It will open on whatever the current month is, but make sure your sheet names are the full name for each month, no abbreviations.

Using the Format function to to get the month name requires that each sheet use the same naming convention for the month. If you do use abbreviations, the 'mmmm' would need to be changed to 'mmm' and the standard three character abbreviation would have to be used on the sheet name tab. But the OP indicated that you are using the full month name so that is how the macro is written.

8

 

[Purple Turtle]

Thank you for the explanation

 

[JLGWhiz]

Thank you for the explanation

You're welcome,
regards, JLG