VBA AutoOpen sub to worksheet # based upon the current month

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
Good EveningEveryone :),

I would like to create an AutoOpen sub that automatically opens up to a specific worksheet based upon the current month.



For example, we are currently in October, so I'd like it to open up toWorksheets(4).

If its November, I'd like it to open up to Worksheets(5).


If its December,I'd like it to open up to Worksheets(6).

If its January,I'd like it to open up to Worksheets(7).

.
. ( & so on continuing upon this consecutive pattern )
.

If its August,I'd like it to open up to Worksheets(14).

If its September,I'd like it to open up to Worksheets(15).



Can someone please help me?



Many thanks inadvance!

R/
pinaceous

 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
903
Try adding to 'ThisWorkbook'

Code:
Private Sub Workbook_Open()

Dim shtname As String

thismonth = Month(Now())

If thismonth = 11 Or thismonth = 12 Then
thismonth = thismonth - 6
Else
thismonth = thismonth + 6
End If

Worksheets(thismonth).Select

End Sub
 
Last edited:

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
I'm getting a Compile error: Wrong number of arguments or invalid property assignment, where it has highlighted "Month" in

Code:
 thismonth = Month(Now())
Any suggestions?

Thank you!
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
903
Not sure on that.... No issues here.

Which version of Office is it? Or does anyone else have a suggestion?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
903
Just noticed something, try declaring thismonth

Code:
Private Sub Workbook_Open()


Dim shtname As String
Dim thismonth As Long


thismonth = Month(Now())


If thismonth = 11 Or thismonth = 12 Then
thismonth = thismonth - 6
Else
thismonth = thismonth + 6
End If


Worksheets(thismonth).Select


End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,455
Office Version
365
Platform
Windows
Maybe
Code:
thismonth = Month(Date)
If you still get the compile error, check that you don't have any subs, functions, or variables called "Month"
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
Oh I see. I have two similar Private Sub Workbook_Open(). I can't seem to combine them because they do two different things. Any suggestions?? Can I have 2 Private Workbook_Open Subs operating at the same time?
 

Forum statistics

Threads
1,078,312
Messages
5,339,422
Members
399,305
Latest member
msklut

Some videos you may like

This Week's Hot Topics

Top