-1

I have a bunch of Month Tabs

What is one way to open a specific one with VBA? I tried using this snippet:

Private Sub Workbook_Open()

Dim ws As Worksheet
Dim mnth As String, dte As String, mday As String

mday = Now() - Weekday(Now(), 3)

mnth = Month(mday)
dte = Day(mday)

tabstr = mnth & "-" & dte

   For Each ws In Worksheets
       If ws.Name = tabstr Then
           ws.Select
           Exit For
       End If
    Next 
End sub

but that's for sheet names that are named after dates formatted as mm-dd, e.g. "11-7" or "11-14".

How do I change this code so it works for sheet names that contain month names like "May", "June", or "July"?

2 Answers 2

2

To automatically bring the current month's tab to the foreground after opening a workbook, you can simply use this:

Private Sub Workbook_Open()
    Sheets(Format(Now(), "mmmm")).Select
End Sub

NOTE: This will only work if the month names are in the same language than the OS.

1

To get the name of the current month:

Sub tretdc()
    tabstr = Format(Now(), "mmmm")
    MsgBox tabstr
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .