Help with excel macro to open worksheets auto based on system date

[mjdunn]

Hi, i was wondering anyone could help.
Being a novice to Macros and VBA, i have an excel workbook which has multiple worksheets. Each worksheet is a day of the month.
Could anyone tell me how to, if i have a excel workbook open and have the system date equal to that of the worksheet day of the month. So if system date changes, the correct worksheet opens.
If possible i would also like it to loop so that if the system date reaches the end of the month it opens the first day of the month-worksheet again when new month starts.

I hope this makes sence, any pointers would be a great help.

Many thanks

 

[rmikesmith]

mjdunn,

If I understand you correctly, when a user opens your workbook you want the worksheet that corresponds to the current day of the month to be activated. The following procedure, which should be called from the Workbook_Open event handler, will do that. In fact, it is set up such that only the current day's sheet is visible.

Sub SelectWorksheet()
Dim wks1 As Worksheet
Dim wks2 As Worksheet

  On Error Resume Next
  Set wks1 = ThisWorkbook.ActiveSheet
  Set wks2 = ThisWorkbook.Worksheets(Day(Date))
  wks2.Visible = xlSheetVisible
  wks2.Activate
  wks1.Visible = xlSheetHidden
  Set wks1 = Nothing
  Set wks2 = Nothing
End Sub

Notes: This procedure assumes that 31 worksheets, named 1 - 31, exist. All but one should be hidden. Keep in mind that this code will not change worksheets as the date rolls over, or if the system date were to be changed while the workbook was open.

Hope this helps.

M. Smith

 

[mjdunn]

Thank you very much M Smith, i will give it ago.

Cheers