Is there a way to create a macro that will show the tabs at the bottom by the
current month? I have the tabs named by month, and only want it to show
January-Current month tabs only as it progresses through the year. So, for
instance, Tabs January, February, March, etc.... through September would be
showing right now, then next month, would include October.

There are a couple different ways you could do this. I chose to use
an array of month names that correlate with the sheet names. This
will allow any sheets that are stuck inbetween months to not be
hidden. Edit this as you see fit.
Sub sheetHider()
Dim mthList As Variant, sht As Worksheet
mthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If Month(Date) = 12 Then
MsgBox "No months to hide"
Else
For mth = Month(Date) + 1 To 12
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = mthList(mth - 1) Then
sht.Visible = xlSheetHidden
End If
Next sht
Next mth
End If
End Sub
Tasha wrote:
Is there a way to create a macro that will show the tabs at the bottom by the
current month? I have the tabs named by month, and only want it to show
January-Current month tabs only as it progresses through the year. So, for
instance, Tabs January, February, March, etc.... through September would be
showing right now, then next month, would include October.

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
End Sub

Note that the year in DateSerial(year,month,day) could be any valid year.


In article ,
Tasha wrote:

Is there a way to create a macro that will show the tabs at the bottom by the
current month? I have the tabs named by month, and only want it to show
January-Current month tabs only as it progresses through the year. So, for
instance, Tabs January, February, March, etc.... through September would be
showing right now, then next month, would include October.

thanks so much, both of these worked!!! The last one serves my purpose
better because I can't have a prompt as the macro is run at night.



"JE McGimpsey" wrote:

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
End Sub

Note that the year in DateSerial(year,month,day) could be any valid year.


In article ,
Tasha wrote:

Is there a way to create a macro that will show the tabs at the bottom by the
current month? I have the tabs named by month, and only want it to show
January-Current month tabs only as it progresses through the year. So, for
instance, Tabs January, February, March, etc.... through September would be
showing right now, then next month, would include October.

one more question, is there a way to select the current month worksheet?

"JE McGimpsey" wrote:

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
End Sub

Note that the year in DateSerial(year,month,day) could be any valid year.


In article ,
Tasha wrote:

Is there a way to create a macro that will show the tabs at the bottom by the
current month? I have the tabs named by month, and only want it to show
January-Current month tabs only as it progresses through the year. So, for
instance, Tabs January, February, March, etc.... through September would be
showing right now, then next month, would include October.

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
Worksheets(Format(DateSerial(2007, nCurrentMonth, 1), "mmmm")).Select
End Sub



In article ,
Tasha wrote:

one more question, is there a way to select the current month worksheet?

"JE McGimpsey" wrote:

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
End Sub

The prompt is not really necessary. I just put it in for
demonstration purposes.
Sub sheetHider()
Dim mthList As Variant, sht As Worksheet
mthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If Month(Date) < 12 Then
For mth = Month(Date) + 1 To 12
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = mthList(mth - 1) Then
sht.Visible = xlSheetHidden
End If
Next sht
Next mth
End If
End Sub

JE McGimpsey wrote:
One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
Worksheets(Format(DateSerial(2007, nCurrentMonth, 1), "mmmm")).Select
End Sub



In article ,
Tasha wrote:

one more question, is there a way to select the current month worksheet?

"JE McGimpsey" wrote:

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
End Sub

Tiny niggle: If the user has manually hidden one or more sheets,
including sheet "Jan", it is possible to get a run-time error with this
technique since at least one sheet must remain visible.



In article . com,
JW wrote:

The prompt is not really necessary. I just put it in for
demonstration purposes.
Sub sheetHider()
Dim mthList As Variant, sht As Worksheet
mthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If Month(Date) < 12 Then
For mth = Month(Date) + 1 To 12
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = mthList(mth - 1) Then
sht.Visible = xlSheetHidden
End If
Next sht
Next mth
End If
End Sub

JE, you are correct. That most certainly could happen. But that
could happen with your technique as well, correct?
JE McGimpsey wrote:
Tiny niggle: If the user has manually hidden one or more sheets,
including sheet "Jan", it is possible to get a run-time error with this
technique since at least one sheet must remain visible.



In article . com,
JW wrote:

The prompt is not really necessary. I just put it in for
demonstration purposes.
Sub sheetHider()
Dim mthList As Variant, sht As Worksheet
mthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If Month(Date) < 12 Then
For mth = Month(Date) + 1 To 12
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = mthList(mth - 1) Then
sht.Visible = xlSheetHidden
End If
Next sht
Next mth
End If
End Sub

Not correct.

With my technique, the January sheet will always be made visible as the
first step, even if the user has hidden it.



In article . com,
JW wrote:

JE, you are correct. That most certainly could happen. But that
could happen with your technique as well, correct?

Another idea is the use of "MonthName"

For m = 1 To 12
Worksheets(MonthName(m, False)).Visible = (i <= nCurrentMonth)

--
Dana DeLouis
Windows XP & Excel 2007


"JE McGimpsey" wrote in message
...
One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
Worksheets(Format(DateSerial(2007, nCurrentMonth, 1), "mmmm")).Select
End Sub



In article ,
Tasha wrote:

one more question, is there a way to select the current month worksheet?

"JE McGimpsey" wrote:

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible =
_
i <= nCurrentMonth
Next i
End Sub

Yeah - MonthName() is a VBA6+ function, so I tend to forget about it
since I develop cross-platform applications, and I still have a
substantial minority of customers using WinXL97.

In article ,
"Dana DeLouis" wrote:

Another idea is the use of "MonthName"

For m = 1 To 12
Worksheets(MonthName(m, False)).Visible = (i <= nCurrentMonth)

Ahhhh, said the blind man. Looked right over that. Smooth code.
JE McGimpsey wrote:
Not correct.

With my technique, the January sheet will always be made visible as the
first step, even if the user has hidden it.



In article . com,
JW wrote:

JE, you are correct. That most certainly could happen. But that
could happen with your technique as well, correct?