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

 

[Pinaceous]

Okay, I actually have 03 Private Sub Workbook codes that work upon opening.


I’ve also tried to designate the Post#6 code as a stand-alone code, for example from Private Sub Workbook_Open() to Sub Workbook_TabOpening() then Call it within a separate Sub Autpen() code but that does not seem to work.


Here are the 03 codes that I can tell, if you can let me know, what you think that would be greatly appreciated!

Private Sub Workbook_Open()
Dim OneSheet As Worksheet
For Each OneSheet In ThisWorkbook.Worksheets
    OneSheet.ScrollArea = "A1:O80"
Next OneSheet
 End Sub

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

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call Move_DV_Input_Message_To(Range(TARGET_RANGE_ANCHOR))
End Sub


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("B7").Select
'ActiveSheet.Range("B7").Select
ActiveWindow.Zoom = 100
    Select Case Sh.Name
    Case Else: Call RefreshRibbon(Tag:="Xron")
    
    End Select
     
End Sub

Thank you,
Pinaceous

 

[Fluff]

You can put the two open events together like

Private Sub Workbook_Open()
    Dim OneSheet As Worksheet
    Dim shtname As String
    Dim thismonth As Long

    For Each OneSheet In ThisWorkbook.Worksheets
        OneSheet.ScrollArea = "A1:O80"
    Next OneSheet
    thismonth = Month(Now())

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

 

[Pinaceous]

That's a big dua for me. I appreciate the clarity. Peace.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Pinaceous]

Hi mrshl9898,


Your code is working out really well.


I have another workbook where I would like to create an AutoOpen sub that automatically opens up upon a different worksheet based from Post#1, based upon the current month.

I tried to toy around with your code from Post##2 but I can't seem to get the numbers right.


Can you help me?

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


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(7).



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

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



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


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




Many thanks again in advance!



R/
pinaceous












Many thanks again in advance!
R/
pinaceous

 

[Fluff]

How about

Private Sub Workbook_Open()
    Dim Sht As Long
    
    Sht = Choose(Month(Date), 9, 11, 13, 15, 17, 19, 21, 23, 25, 3, 5, 7)
    Worksheets(Sht).Activate
End Sub

 

[Pinaceous]

Hey Fluff!

Thanks very much! Could you explain in words how your code works? I know what I asked and you answered but looking at the code I'm baffled. For example, how does it know October is for worksheet(9) and not lets say January? I'm good with the code just want to know the how, that's all.

Many thanks & take care!
Pinaceous

 

[Fluff]

This part

Month(Date)

will give you the month number (ie 1 to 12) which is used as the index value in the choose function & that will then return the appropriate value from the list.
So month 1 will return 9, month 2 will return 11 etc.

 

[Pinaceous]

Oh I get it! Thanks Fluff

 

[Fluff]