Run macro across multiple worksheets - automatically open at today's date

[HinaLennox]

Hi all,

I'm completely new to the world of macros, and am struggling to get a macro to apply across multiple worksheets in the one Excel file.

Basically 14/28 of the sheets feature a kind of calendar, and I'd like the sheets to always open on the column displaying today's date (so nobody needs to scroll to the date).

The following Macro works for the one sheet named in it, but when I try to duplicate it (and change the sheet name) or use a code to make it apply across all sheets, I get error messages and it doesn't work.
Can anyone help me out? Is there a way to list the names of multiple sheets in the macro?

Private Sub Workbook_Open()
Worksheets("Leela Sch").Select
x = Day(Date)
Worksheets("Leela Sch").Rows(1).Find(What:=x, LookIn:=xlValues).Activate
Application.Goto Selection, True
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

In a standard module:

Sub GoToDay()
  Dim x As Long
  Dim rFound As Range
  
  x = Day(Date)
  Set rFound = ActiveSheet.Rows(1).Find(What:=x, LookIn:=xlValues, LookAt:=xlWhole)
  If Not rFound Is Nothing Then Application.Goto rFound, True
End Sub

In the ThisWorkbook module:

Private Sub Workbook_Open()
  GoToDay
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  GoToDay
End Sub