Opening a file to a dynamic sheet
 

I am using a master file to access many other files that my department uses
often. These files have sheets named as the month and year (i.e. September
2005, October 2005, etc.). When opening these files, the current month's
sheet is not always (or ever) the active sheet which would be the desired
result.

How can I, upon opening these workbooks, active the sheet corresponding to
the current month?

Thanks

Opening a file to a dynamic sheet
 

One way:

Option Explicit
Sub auto_open()
Dim myString As String

myString = Format(Date, "mmmm yyyy")
On Error Resume Next
Application.Goto Worksheets(myString).Range("A1")
If Err.Number < 0 Then
MsgBox "No worksheet named: " & myString
Err.Clear
End If
On Error GoTo 0

End Sub



JNW wrote:

I am using a master file to access many other files that my department uses
often. These files have sheets named as the month and year (i.e. September
2005, October 2005, etc.). When opening these files, the current month's
sheet is not always (or ever) the active sheet which would be the desired
result.

How can I, upon opening these workbooks, active the sheet corresponding to
the current month?

Thanks

--

Dave Peterson

Opening a file to a dynamic sheet
 

Try this each time your master opens a workbook file:

Sub OpenCurrentMonth()
Dim CurMnth As String

CurMnth = Format(Date, "mmmm yyyy")
Worksheets(CurMnth).Activate

If ActiveSheet.Name < CurMnth Then
MsgBox ("There is no worksheet for this Month")
Exit Sub
End If
End Sub

Mike F
"JNW" wrote in message
...
I am using a master file to access many other files that my department uses
often. These files have sheets named as the month and year (i.e.
September
2005, October 2005, etc.). When opening these files, the current month's
sheet is not always (or ever) the active sheet which would be the desired
result.

How can I, upon opening these workbooks, active the sheet corresponding to
the current month?

Thanks

Opening a file to a dynamic sheet
 

"Mike Fogleman" wrote:

Try this each time your master opens a workbook file:

Sub OpenCurrentMonth()
Dim CurMnth As String

CurMnth = Format(Date, "mmmm yyyy")
Worksheets(CurMnth).Activate

If ActiveSheet.Name < CurMnth Then
MsgBox ("There is no worksheet for this Month")
Exit Sub
End If
End Sub

Mike F
"JNW" wrote in message
...
I am using a master file to access many other files that my department uses
often. These files have sheets named as the month and year (i.e.
September
2005, October 2005, etc.). When opening these files, the current month's
sheet is not always (or ever) the active sheet which would be the desired
result.

How can I, upon opening these workbooks, active the sheet corresponding to
the current month?

Thanks

Opening a file to a dynamic sheet
 

Oops...

Thanks guys. I ended up using a combination of both of your responses.
Works great.

JNW

"Mike Fogleman" wrote:

Try this each time your master opens a workbook file:

Sub OpenCurrentMonth()
Dim CurMnth As String

CurMnth = Format(Date, "mmmm yyyy")
Worksheets(CurMnth).Activate

If ActiveSheet.Name < CurMnth Then
MsgBox ("There is no worksheet for this Month")
Exit Sub
End If
End Sub

Mike F
"JNW" wrote in message
...
I am using a master file to access many other files that my department uses
often. These files have sheets named as the month and year (i.e.
September
2005, October 2005, etc.). When opening these files, the current month's
sheet is not always (or ever) the active sheet which would be the desired
result.

How can I, upon opening these workbooks, active the sheet corresponding to
the current month?

Thanks