Help Please

[santaviga]

I have a macro that I need to input data into Footers on worksheets, I am
having a problem to begin with saying script out of range, any help, I also
need this this macro to run automatic when the document is opened, is this at
all possible, please have a look at my code and revise if needed. I have a
beforesave eveny operating in this workbook so before save is not an option
unless I can call this macro before save like I do with protect sheets.

Many Many thanks

Mark

Sub PageSetUp()
Dim SH1 As Object
Dim SH2 As Object
With Me
Set SH1 = .Sheets("Sheet3")
Set SH2 = .Sheets("Sheet8")
End With
SH1.PageSetUp.RightFooter = Format(Now, "dd-mmm-yy")
SH2.PageSetUp.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

 

[JLGWhiz]

This worked for me using sheet 1 and 2, so if you have a sheet 3 and sheet 8
it should work for you.

Private Sub Workbook_Open()
Dim SH1 As Object
Dim SH2 As Object
With ActiveWorkbook
Set SH1 = .Sheets("Sheet3")
Set SH2 = .Sheets("Sheet8")
End With
SH1.PageSetUp.RightFooter = Format(Now, "dd-mmm-yy")
SH2.PageSetUp.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy")
End Sub

 

[JLGWhiz]

P.S. The code should be put in the ThisWorkbook code module.

 

[santaviga]

I am getting a subscript out of range on mine. any ideas

 

[santaviga]

Hi, I have the code in the workbook not individual sheets it is returning
subscript out of range at Set SH1 = .Sheets("Sheet3")

Thanks

 

[santaviga]

Its OK I fixed it, Sheets are named instead of being sheet 3 and sheet 8

Sorry and Many thanks

 

[JLGWhiz]

It is telling you that you don't have a sheet named "Sheet3", or at least it
can't find one named that. The easy way is to give the sheets you want to
work with a specific name like DayShift and ReliefShift, then change the
reference in your code accordingly.

 

[Gary Keramidas]

my guess is that you changed the sheet name and the codename is sheet3, but i
don't know for sure.

Private Sub Workbook_Open()
Dim SH1 As Object
Dim SH2 As Object
With ActiveWorkbook
Set SH1 = Sheet3
Set SH2 = .Sheets("Sheet8")
End With
SH1.PageSetUp.RightFooter = Format(Now, "dd-mmm-yy")
SH2.PageSetUp.RightFooter = "Relief Shifts " & Format(Now, "dd-mmm-yy")
End Sub