help with macro

[santaviga]

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

 

[Norman Jones]

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

 

[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. Any ideas

 

[santaviga]

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]

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?

 

[santaviga]

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

 

[santaviga]

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]

Hi Santaviga,

Change:

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

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

 

[Norman Jones]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[santaviga]

Have you got any ideas on the code Rick.

Thanks

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

 

[santaviga]

This has been done but still error.

Hi Santaviga,

Change:


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

 

[Norman Jones]

Hi Rick,

=============

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

 

[Norman Jones]

Hi Santaviga,

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Norman Jones]

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

 

[Norman Jones]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

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.

 

[santaviga]

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]

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

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