VBA AutoOpen sub to worksheet # based upon the current month

[Pinaceous]

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]

Try adding to 'ThisWorkbook'

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

 

[Pinaceous]

Hi mrshl9898,

That is short and sweet!

Thank you!
pinaceous

 

[Pinaceous]

I'm getting a Compile error: Wrong number of arguments or invalid property assignment, where it has highlighted "Month" in

 thismonth = Month(Now())

Any suggestions?

Thank you!

 

[mrshl9898]

Not sure on that.... No issues here.

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

 

[mrshl9898]

Just noticed something, try declaring thismonth

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]

Maybe

thismonth = Month(Date)

If you still get the compile error, check that you don't have any subs, functions, or variables called "Month"

 

[Pinaceous]

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?

 

[mrshl9898]

Best to post the codes

 

[Pinaceous]

Copy that.