Opening File To Current Date Sheet

[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

 

[J.E. McGimpsey]

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

 

[Dave Peterson]

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>.)

 

[Mike]

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

 

[raymondsum]

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

 

[J.E. McGimpsey]

Change Date to Date - 1

 

[Dave Peterson]

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

 

[Mike]

Thank you all, works great.

Change Date to Date - 1

 

[raymondsum]

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

 

[J.E. McGimpsey]

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).

ref: Workbook Events:
http://www.mvps.org/dmcritchie/excel/event.htm#blueboxWB

 

[raymondsum]

It works.

Thank you very much.

Raymon

 

[raymondsum]

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

 

[Dave Peterson]

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.

 

[raymondsum]

Dave,

Thanks a lot.

Raymon