Good point... Go with Bob's code... Mine would be unhappy when the month
rolled over... No visisble sheets can be a problem... :)

"Bob Phillips" wrote:

Jim,

You probably need to modify that Open code for subsequent runs, when only
one sheet is showing, and the month changes

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name = strCurrentMonth Then
wks.Visible = xlSheetVisible
Else
wks.Visible = xlSheetHidden
End If
Next wks
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim Thomlinson" wrote in message
...
Here is smoe code to do the hiding thing that you want.

Option Explicit

Private Sub Workbook_Open()
Dim wks As Worksheet
Dim strCurrentMonth As String

strCurrentMonth = MonthString
For Each wks In Worksheets
If wks.Name < strCurrentMonth Then wks.Visible = xlSheetHidden
Next wks
End Sub

Private Function MonthString() As String
Dim strMonth As String
Dim intMonth As Integer

intMonth = Month(Date)

Select Case intMonth
Case 1
strMonth = "Jan"
Case 2
strMonth = "Feb"
Case 3
strMonth = "Mar"
Case 4
strMonth = "Apr"
Case 5
strMonth = "May"
Case 6
strMonth = "Jun"
Case 7
strMonth = "Jul"
Case 8
strMonth = "Aug"
Case 9
strMonth = "Sep"
Case 10
strMonth = "Oct"
Case 11
strMonth = "Nov"
Case 12
strMonth = "Dec"

End Select
MonthString = strMonth
End Function

Paste this into the ThisWorkbook object in the VB Editor. If you want to
modify your own code the Workbook_Open event is found in the ThisWorkbook
object. Click on the Drop down that says General and where it says
Declarations select the event you want...

Try both my code and yours and play with them a bit. You can step through
them using the F8 key...

HTH

"Natalie" wrote:

Hello All,

I have numerous sheets and I would like to show or hide
them based on the month. IE. If it is Jan hide all my
other sheets apart from the Jan sheet, this is to stop
people seeing the data.

I got the following code from an MVP from this site. But
I don't know what to do with it. I'm a beginner in Vb so
please bear with me!!

Dim MyMonth As Integer
MyMonth = Month(Now())

Select Case MyMonth

Case 1 'If month number is 1 (Jan)
Sheets("Feb").Visible = False
Sheets("Mar").Visible = False
Case 2 'If Month number is 2 (Feb)
Sheets("Jan").Visible = False
Sheets("March").Visible = False
Case 3 'If Month number is 3 (Mar)
Sheets("Jan").Visible = False
Sheets("Mar").Visible = True
Sheets("Feb").Visible = False
End Select
End Sub

The Expert suggested I put this in the Open workbook
event??? Where is that please?

Thank for reading

Natalie