VBA Workbook_Open for Specific Date (Current Date)

Question

I had created a workbook with sheet1 named as 1 and sheet2 as 2 and continue until 3 (as Month). I had a Date on each sheet on B2 Cell and my date format is 01-Jan-2019 but other sheets i use ='1'!+1 formula for continues of Date. I use this VBA code in "ThisWorkbook":

 Private Sub Workbook_Open()
    Worksheets("26").Activate
    Range("B1:B3").Find(Date).Select
End Sub

All I want is Open the specific Sheet depending on current Date.

Thank You Guys!! Appreciate.......

Can you explain what you mean by "continue until 3 (as Month)"? — BigBen
Sorry for the typo error. Thats "continue until 31 (end of month). Sorry again for the error. — Sagar Rana

simple-solution simple-solution · Accepted Answer · 2019-01-26T16:41:41

'https://stackguides.com/questions/54374272/vba-workbook-open-for-specific-date-current-date
'VBA Workbook_Open for Specific Date (Current Date)

Sub CreateWorkbookWith31Sheets()
Dim wb As Workbook
Dim ws As Worksheets
Dim iCt As Integer
Set wb = Workbooks.Add
With wb.Worksheets
For iCt = 1 To .Count
    wb.Worksheets(iCt).Name = iCt
    wb.Worksheets(iCt).Range("B2").Value = DateSerial(2019, 1, iCt)
    wb.Worksheets(iCt).Range("B2").NumberFormat = "m/d/yyyy"
Next iCt
For iCt = iCt To 31
    .Add After:=wb.Worksheets(.Count)
    wb.Worksheets(iCt).Name = iCt
    wb.Worksheets(iCt).Range("B2").Value = DateSerial(2019, 1, iCt)
    wb.Worksheets(iCt).Range("B2").NumberFormat = "m/d/yyyy"
Next iCt
End With
ActiveWorkbook.SaveAs Filename:="C:\mySpecialFolder\my31days.xlsb", _  
    FileFormat:=xlExcel12
End Sub

'Copy this Subroutine to "ThisWorkbook" of the newly created workbook
Private Sub Workbook_Open()
Dim actDay As Integer
    actDay = Format(Date, "d")
    Worksheets(actDay).Activate
End Sub

=> This workbook will open and activate the
. sheet "26" if today is the 26th of the month!

VBA Workbook_Open for Specific Date (Current Date)

1 Answers