Hi,

I need some help with a macro to run before save on 2 sheets, please see the
macro I have input but it is not working on save.

macro is in sheet 8

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

and macro in sheet 3

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

I need both of these macros to run when I save on the separate sheets, can
anyone help me on this matter

Kind regards

Hi Santaviga.

A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook
module, and there can only be one such macro in the workbook.

Therefore, cancel the two procedures
in the sheet modules and post the
following code inyo the Thisworkbook
module:

'=========
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim mySheets As Sheets

Set mySheets = Me.Sheets(Array("Sheet8", "Sheet1"))

For Each SH In mySheets
SH.PageSetup.RightFooter = "Relief Shifts " _
& Format(Now, "dd-mmm-yy")
Next SH
End Sub
'<<=========


---
Regards.
Norman


"santaviga" wrote in message
...
Hi,

I need some help with a macro to run before save on 2 sheets, please see
the
macro I have input but it is not working on save.

macro is in sheet 8

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

and macro in sheet 3

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

I need both of these macros to run when I save on the separate sheets, can
anyone help me on this matter

Kind regards

Thanks for your response, I need the footers in both sheets to be different,
sheet 1 relief shifts and date

Sheet 8 only the date. Any ideas

"Norman Jones" wrote:

Hi Santaviga.

A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook
module, and there can only be one such macro in the workbook.

Therefore, cancel the two procedures
in the sheet modules and post the
following code inyo the Thisworkbook
module:

'=========
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim mySheets As Sheets

Set mySheets = Me.Sheets(Array("Sheet8", "Sheet1"))

For Each SH In mySheets
SH.PageSetup.RightFooter = "Relief Shifts " _
& Format(Now, "dd-mmm-yy")
Next SH
End Sub
'<<=========


---
Regards.
Norman


"santaviga" wrote in message
...
Hi,

I need some help with a macro to run before save on 2 sheets, please see
the
macro I have input but it is not working on save.

macro is in sheet 8

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

and macro in sheet 3

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

I need both of these macros to run when I save on the separate sheets, can
anyone help me on this matter

Kind regards

Tried the code but reporting abiguous error, I do have another before save
macro in the workbook also will this have anything to do with it?

Mark

"Norman Jones" wrote:

Hi Santaviga.

A WorkbookBbeforeSave event macro must reside in the workbook's Thisworkbook
module, and there can only be one such macro in the workbook.

Therefore, cancel the two procedures
in the sheet modules and post the
following code inyo the Thisworkbook
module:

'=========
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim mySheets As Sheets

Set mySheets = Me.Sheets(Array("Sheet8", "Sheet1"))

For Each SH In mySheets
SH.PageSetup.RightFooter = "Relief Shifts " _
& Format(Now, "dd-mmm-yy")
Next SH
End Sub
'<<=========


---
Regards.
Norman


"santaviga" wrote in message
...
Hi,

I need some help with a macro to run before save on 2 sheets, please see
the
macro I have input but it is not working on save.

macro is in sheet 8

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

and macro in sheet 3

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As
Boolean)
ActiveSheet.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub

I need both of these macros to run when I save on the separate sheets, can
anyone help me on this matter

Kind regards

Hi Santaviga.


=============
Thanks for your response, I need the footers in both sheets to be different,
sheet 1 relief shifts and date

Sheet 8 only the date.
=============

The footers were identical because that
is what you showed, However, try:

'=========
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim SH2 As Object

With Me
Set SH = .Sheets("Sheet1") '<<==== CHANGE
Set SH2 = .Sheets("Sheet3") '<<==== CHANGE
End With

SH.PageSetup.RightFooter = _
"Relief Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE

SH2.PageSetup.RightFooter = _
"Other Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE
End Sub
'<<=========

=============
Tried the code but reporting abiguous error, I do have another before save
macro in the workbook also will this have anything to do with it?
=============

See my comment:

A WorkbookBbeforeSave event macro must reside
in the workbook's Thisworkbook
module, and there can only be one such macro in the
workbook.

You will need to amalgamate your existing
Workbook_BeforeSave procedure with the
above code.

If you experience difficulty in amalgamating
the two procedures, post the code for each
in a response here.

Incidentally, do you have a good reason for
setting the footers before every save operation?



---
Regards.
Norman

Hi, I keep getting this ambigous name error with regards to private sub
workbook before save

"Norman Jones" wrote:

Hi Santaviga.


=============
Thanks for your response, I need the footers in both sheets to be different,
sheet 1 relief shifts and date

Sheet 8 only the date.
=============

The footers were identical because that
is what you showed, However, try:

'=========
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim SH2 As Object

With Me
Set SH = .Sheets("Sheet1") '<<==== CHANGE
Set SH2 = .Sheets("Sheet3") '<<==== CHANGE
End With

SH.PageSetup.RightFooter = _
"Relief Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE

SH2.PageSetup.RightFooter = _
"Other Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE
End Sub
'<<=========

=============
Tried the code but reporting abiguous error, I do have another before save
macro in the workbook also will this have anything to do with it?
=============

See my comment:

A WorkbookBbeforeSave event macro must reside
in the workbook's Thisworkbook
module, and there can only be one such macro in the
workbook.

You will need to amalgamate your existing
Workbook_BeforeSave procedure with the
above code.

If you experience difficulty in amalgamating
the two procedures, post the code for each
in a response here.

Incidentally, do you have a good reason for
setting the footers before every save operation?



---
Regards.
Norman

Hi Santaviga,

Hi, I keep getting this ambigous name error with regards to private sub
workbook before save

As previously explained, only one
Workbook_BeforeSave event procedure
may reside in the Workbook's
ThisWorkbook module.

Your error suggests that you have more
than one such procedure. In such case,
as explained, it will be necessary either to
delete one of the procedures or, more
probably, amalgamate the code to produce
a single routine.


---
Regards.
Norman

Here is the code I have input:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim SH2 As Object
With Me
Set SH = .Sheets("Sheet3")
Set SH2 = .Sheets("Sheet8")
End With
SH.PageSetup.RightFooter = Format(Now, "dd-mmm-yy")
SH2.PageSetup.RightFooter = "Relief Shifts " & Format(Now,
"dd-mmm-yy")
End Sub


"Norman Jones" wrote:

Hi Santaviga.


=============
Thanks for your response, I need the footers in both sheets to be different,
sheet 1 relief shifts and date

Sheet 8 only the date.
=============

The footers were identical because that
is what you showed, However, try:

'=========
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim SH As Object
Dim SH2 As Object

With Me
Set SH = .Sheets("Sheet1") '<<==== CHANGE
Set SH2 = .Sheets("Sheet3") '<<==== CHANGE
End With

SH.PageSetup.RightFooter = _
"Relief Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE

SH2.PageSetup.RightFooter = _
"Other Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE
End Sub
'<<=========

=============
Tried the code but reporting abiguous error, I do have another before save
macro in the workbook also will this have anything to do with it?
=============

See my comment:

A WorkbookBbeforeSave event macro must reside
in the workbook's Thisworkbook
module, and there can only be one such macro in the
workbook.

You will need to amalgamate your existing
Workbook_BeforeSave procedure with the
above code.

If you experience difficulty in amalgamating
the two procedures, post the code for each
in a response here.

Incidentally, do you have a good reason for
setting the footers before every save operation?



---
Regards.
Norman

Hi Santaviga,

Change:

"Other Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE

to
Format(Now, "dd-mmm-yy")


---
Regards.
Norman

Change:

"Other Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE

to
Format(Now, "dd-mmm-yy")

I don't think so... notice the line continuation character (the trailing
underbar character) on the preceding line... the & is there to concatenate
the output from the Format command with that preceding continued line.

Rick

This has been done but still error.

"Norman Jones" wrote:

Hi Santaviga,

Change:

"Other Shifts " _
& Format(Now, "dd-mmm-yy") '<<==== CHANGE

to
Format(Now, "dd-mmm-yy")


---
Regards.
Norman