help with macro
 

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

help with macro
 

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

help with macro
 

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

help with macro
 

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

help with macro
 

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

help with macro
 

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

help with macro
 

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

help with macro
 

Hi Santaviga,

Change:

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

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


---
Regards.
Norman

help with macro
 

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

help with macro
 

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

help with macro
 

Have you got any ideas on the code Rick.

Thanks

"Rick Rothstein (MVP - VB)" wrote:

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

help with macro
 

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

help with macro
 

Hi Rick,

=============
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.
=============

I was responding. belatedly, to the
OP's request:

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 instruction:

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

returns a date string of the type

30-Apr-08

- as requested.

What do you disagree with precisely




---
Regards.
Norman

help with macro
 

Hi Santaviga,

Try to copy / paste the code from the
Thisworkbook module.



---
Regards.
Norman


"santaviga" wrote in message
...
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

help with macro
 

What do you disagree with precisely

Your message said this...

Change:

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

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

The only difference I see is in what you posted as the change was the
removal of the ampersand (the Format statements appear to be identical), so
I assumed you were "correcting" what you thought was a "extra" ampersand on
the line. My response was saying that the ampersand is necessary because of
the line continuation character.

Rick

help with macro
 

Hi Santaviga,

Have you got any ideas on the code Rick.

Without in any way wishing to pre-empt
any reponse from Rick, the suggested
code works for me without any problem


---
Regards.
Norman

help with macro
 

Hi Rick,

If you backtrack in the thread, you will
see that I had suggested:

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

Noting the OP's desire only to display the
date in that particular footer, I suggested:

===========
Change:

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

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

The result of such a change, if implemented,
would be to return the following instruction:

SH2.PageSetup.RightFooter = _
Format(Now, "dd-mmm-yy")

which would appear to present no syntactic
problems and works for me.


---
Regards.
Norman

help with macro
 

Yes, I see what you meant now... I think I became fixated on the
"<<===CHANGE" comment and missed the bigger point of your message. Sorry for
the distraction in this thread.

Rick


"Norman Jones" wrote in message
...
Hi Rick,

If you backtrack in the thread, you will see that I had suggested:

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

Noting the OP's desire only to display the date in that particular footer,
I suggested:

===========
Change:

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

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

The result of such a change, if implemented,
would be to return the following instruction:

SH2.PageSetup.RightFooter = _
Format(Now, "dd-mmm-yy")

which would appear to present no syntactic
problems and works for me.


---
Regards.
Norman

help with macro
 

Hi Norman,

I have copied and pasted the code, returning ambiguous error name in before
save, as I said I have another before save macro running, will this affect
the code?


Regards

"Norman Jones" wrote:

Hi Santaviga,

Try to copy / paste the code from the
Thisworkbook module.



---
Regards.
Norman


"santaviga" wrote in message
...
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

help with macro
 

Hi Santaviga,

You misunderstand me!

Please copy all the code from the
Thisworkbook module and paste it
into a reply in this thread.

In this way, we can see all of your
code and give you a definitive version
to resolve your problem.



---
Regards.
Norman

"santaviga" wrote in message
...
Hi Norman,

I have copied and pasted the code, returning ambiguous error name in
before
save, as I said I have another before save macro running, will this affect
the code?


Regards

"Norman Jones" wrote:

Hi Santaviga,

Try to copy / paste the code from the
Thisworkbook module.



---
Regards.
Norman


"santaviga" wrote in message
...
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

help with macro
 

Sorry for the misunderstanding, seemingly you cant have 2 before save events
or you will get an ambiguous error, any ideas on resloving. Below is all my
code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Call ProtectAllSheets
End Sub

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="****"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="****"
Next n
Application.ScreenUpdating = True
End Sub

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

Many thanks


"Norman Jones" wrote:

Hi Santaviga,

You misunderstand me!

Please copy all the code from the
Thisworkbook module and paste it
into a reply in this thread.

In this way, we can see all of your
code and give you a definitive version
to resolve your problem.



---
Regards.
Norman

"santaviga" wrote in message
...
Hi Norman,

I have copied and pasted the code, returning ambiguous error name in
before
save, as I said I have another before save macro running, will this affect
the code?


Regards

"Norman Jones" wrote:

Hi Santaviga,

Try to copy / paste the code from the
Thisworkbook module.



---
Regards.
Norman


"santaviga" wrote in message
...
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

help with macro
 

Also on this the subscript is out of range.

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


"santaviga" wrote:

Sorry for the misunderstanding, seemingly you cant have 2 before save events
or you will get an ambiguous error, any ideas on resloving. Below is all my
code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Call ProtectAllSheets
End Sub

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="****"
Next n
Application.ScreenUpdating = True
End Sub

Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Unprotect Password:="****"
Next n
Application.ScreenUpdating = True
End Sub

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

Many thanks


"Norman Jones" wrote:

Hi Santaviga,

You misunderstand me!

Please copy all the code from the
Thisworkbook module and paste it
into a reply in this thread.

In this way, we can see all of your
code and give you a definitive version
to resolve your problem.



---
Regards.
Norman

"santaviga" wrote in message
...
Hi Norman,

I have copied and pasted the code, returning ambiguous error name in
before
save, as I said I have another before save macro running, will this affect
the code?


Regards

"Norman Jones" wrote:

Hi Santaviga,

Try to copy / paste the code from the
Thisworkbook module.



---
Regards.
Norman


"santaviga" wrote in message
...
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

help with macro
 

Got it sorted thanks for your help.

Many thanks

Mark

"Norman Jones" wrote:

Hi Santaviga,

You misunderstand me!

Please copy all the code from the
Thisworkbook module and paste it
into a reply in this thread.

In this way, we can see all of your
code and give you a definitive version
to resolve your problem.



---
Regards.
Norman

"santaviga" wrote in message
...
Hi Norman,

I have copied and pasted the code, returning ambiguous error name in
before
save, as I said I have another before save macro running, will this affect
the code?


Regards

"Norman Jones" wrote:

Hi Santaviga,

Try to copy / paste the code from the
Thisworkbook module.



---
Regards.
Norman


"santaviga" wrote in message
...
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