Opening File To Current Date Sheet

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello,

Using Excel XP. I have a workbook that contains the dates of the month as
separate worksheets, e.g.,
Nov1, Nov2, Nov3 and so on to Nov30. How can I have Excel go to the current
day's sheet when I open up the file.
Thanx in advance,

Michael
 
One way:

Put this in your ThisWorkbook code module (right-click on the
workbook's title bar and choose View Code):

Private Sub Workbook_Open()
On Error Resume Next
Sheets(Format(Date, "mmmdd")).Activate
On Error GoTo 0
End Sub
 
Sheets(Format(Date, "mmmd")).Activate

would get Nov1 through Nov9.

I dropped the leading d in the day code. (Or maybe the trailing d <vbg>.)
 
Thanks for the help. How would I set it to open the file for the previous
day? Like if I opened the file today, Nov24 it would open up to Nov23.
thanks again
 
Hello,

It cant work for me after i copy vb in worksheet (Excel XP).

Private Sub Workbook_Open()
On Error Resume Next
Sheets(Format(Date, "mmmdd")).Activate
On Error GoTo 0
End Sub

My example, the worksheets were Nov24, Nov25, Nov26. Is it necessary to
copy vb into each worksheet or not?

Thanks

Raymond
 
Make sure you put in under the ThisWorkbook module--not under the worksheets and
not in a general module.
 
Dear Dave,

Sorry! I am using Excel2000, not ExcelXp. Is it not such function i
Excel2000?

When i right-click on the workbook, there was no "View Code function'
it only pop up
"T C P S I D N M F K H (shortform)"
But when i select 'sheet1' and right-click then pop up "View Code".

Actually, you said that to " make sure you put in under th
ThisWorkbook module--not under the worksheets and
not in a general module. "

How do i use this function or i misunderstand your suggestion?

Thanks in advance.

Raymon
 
From David McRitchie's site:
Unlike regular macros which are installed in regular modules, Workbook Events
are installed in ThisWorkBook the following manner:  F11 (Visual Basic
Editor), Ctrl+R (VBA Project), under the name of your workbook you see
Microsoft Excel Objects, then before Modules you see ThisWorkBook,
doubleclick and paste the code into the code window (F7).
Click to expand...

ref: Workbook Events:
http://www.mvps.org/dmcritchie/excel/event.htm#blueboxWB
 
How to write vb adding one (1) to specific cell when opening file?

eg in cell A1=100 ; close file ; open file in cell A1 = 101 (100+1)

Any ideas?

Raymond
 
In the workbook_open event (or in the auto_open sub):

Option Explicit
Private Sub Workbook_Open()
With Worksheets("sheet1").Range("A1")
If IsNumeric(.Value) Then
.Value = .Value + 1
Else
MsgBox "cannot increment cell"
End If
End With
End Sub

Remember the value isn't really incremented for next time until you save the
workbook.
 
Back
Top