Attempting to print some reports from a Pivot Table and should like to use a
cell value as a footer. Is it possible to quote a cell value in the Footers?

Sub CellInFooter()
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").text
End With
End Sub


Gord Dibben MS Excel MVP



On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
Cell (E-Mail Removed)> wrote:

>Attempting to print some reports from a Pivot Table and should like to use a
>cell value as a footer. Is it possible to quote a cell value in the Footers?

Gordon,
Thanks for the advise, having never used code before, I presume I have to go
into
Alt+Shift+F11.
When I seach search for Footer I found the followng line. I have attempted
to change it but the code does not appear to work. Can you advise what I am
doing wrong please?

{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
\PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
table

Thanks
Richard


"Gord Dibben" wrote:

> Sub CellInFooter()
> With ActiveSheet
> .PageSetup.CenterFooter = .Range("A1").text
> End With
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
>
>
> On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
> Cell (E-Mail Removed)> wrote:
>
> >Attempting to print some reports from a Pivot Table and should like to use a
> >cell value as a footer. Is it possible to quote a cell value in the Footers?
>
>

This is a macro to be run by hitting a button or shortcut key.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project by name and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

An alternative to running the macro manually is to run it whenever you hit the
Print command.

Instead of pasting the code into a General Module, double-click on the
Thisworkbook module when in the VBE

Paste this code into that module.............

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").Text
End With
End Sub

Will run whenever you print.


Gord Dibben MS Excel MVP

On Fri, 14 Jul 2006 08:05:02 -0700, Footers and Cell references
<(E-Mail Removed)> wrote:

>Gordon,
>Thanks for the advise, having never used code before, I presume I have to go
>into
>Alt+Shift+F11.
>When I seach search for Footer I found the followng line. I have attempted
>to change it but the code does not appear to work. Can you advise what I am
>doing wrong please?
>
>{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
> \PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
>table
>
>Thanks
>Richard
>
>
>"Gord Dibben" wrote:
>
>> Sub CellInFooter()
>> With ActiveSheet
>> .PageSetup.CenterFooter = .Range("A1").text
>> End With
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>>
>>
>> On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
>> Cell (E-Mail Removed)> wrote:
>>
>> >Attempting to print some reports from a Pivot Table and should like to use a
>> >cell value as a footer. Is it possible to quote a cell value in the Footers?
>>
>>

Gord Dibben MS Excel MVP

Hi Gord!

Found your reply in the Newsgroup. Great reply! It works great on my doc on
my machine, the problem is it won't work for anyone else without tweaking.
I saved the VB code told my user to test it...it failed. I have the the
Private Sub Workbook_BeforePrint and the Sub CellInFooter() in This Workbook.
Even changed the Sub to a Private Sub. Still no luck.

The worksheet has a running total at cell W178. I can get that to appear.
But when he opens the file on his machine, it will not update the footer. I
checked the security and had it set to Medium. Nada.

Any clues? Thanks for any ideas!


"Gord Dibben" wrote:

> This is a macro to be run by hitting a button or shortcut key.
>
> If not familiar with VBA and macros, see David McRitchie's site for more on
> "getting started".
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> In the meantime..........
>
> First...create a backup copy of your original workbook.
>
> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>
> Hit CRTL + R to open Project Explorer.
>
> Find your workbook/project by name and select it.
>
> Right-click and Insert>Module. Paste the code in there. Save the
> workbook and hit ALT + Q to return to your workbook.
>
> Run the macro by going to Tool>Macro>Macros.
>
> You can also assign this macro to a button or a shortcut key combo.
>
> An alternative to running the macro manually is to run it whenever you hit the
> Print command.
>
> Instead of pasting the code into a General Module, double-click on the
> Thisworkbook module when in the VBE
>
> Paste this code into that module.............
>
> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> With ActiveSheet
> .PageSetup.CenterFooter = .Range("A1").Text
> End With
> End Sub
>
> Will run whenever you print.
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 14 Jul 2006 08:05:02 -0700, Footers and Cell references
> <(E-Mail Removed)> wrote:
>
> >Gordon,
> >Thanks for the advise, having never used code before, I presume I have to go
> >into
> >Alt+Shift+F11.
> >When I seach search for Footer I found the followng line. I have attempted
> >to change it but the code does not appear to work. Can you advise what I am
> >doing wrong please?
> >
> >{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
> > \PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
> >table
> >
> >Thanks
> >Richard
> >
> >
> >"Gord Dibben" wrote:
> >
> >> Sub CellInFooter()
> >> With ActiveSheet
> >> .PageSetup.CenterFooter = .Range("A1").text
> >> End With
> >> End Sub
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >>
> >>
> >> On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
> >> Cell (E-Mail Removed)> wrote:
> >>
> >> >Attempting to print some reports from a Pivot Table and should like to use a
> >> >cell value as a footer. Is it possible to quote a cell value in the Footers?
> >>
> >>
>
> Gord Dibben MS Excel MVP
>

Couple of points...........

1. The Sub CellInFooter() goes into a standard or general module, not
Thisworkbook module and is run by a button or shortcut key combo or
Tools>Macro>Macros.

2. The BeforePrint code is event code and goes into the Thisworkbook module and
will fire when you hit the "Print" button.

Is the other user enabling macros when opening the workbook?

Does the workbook open for user but can't use the macro or event code won't
fire?


Gord

On Tue, 10 Apr 2007 13:26:01 -0700, smhish <(E-Mail Removed)>
wrote:

>Hi Gord!
>
>Found your reply in the Newsgroup. Great reply! It works great on my doc on
>my machine, the problem is it won't work for anyone else without tweaking.
>I saved the VB code told my user to test it...it failed. I have the the
>Private Sub Workbook_BeforePrint and the Sub CellInFooter() in This Workbook.
> Even changed the Sub to a Private Sub. Still no luck.
>
>The worksheet has a running total at cell W178. I can get that to appear.
>But when he opens the file on his machine, it will not update the footer. I
>checked the security and had it set to Medium. Nada.
>
>Any clues? Thanks for any ideas!
>
>
>"Gord Dibben" wrote:
>
>> This is a macro to be run by hitting a button or shortcut key.
>>
>> If not familiar with VBA and macros, see David McRitchie's site for more on
>> "getting started".
>>
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>> In the meantime..........
>>
>> First...create a backup copy of your original workbook.
>>
>> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>>
>> Hit CRTL + R to open Project Explorer.
>>
>> Find your workbook/project by name and select it.
>>
>> Right-click and Insert>Module. Paste the code in there. Save the
>> workbook and hit ALT + Q to return to your workbook.
>>
>> Run the macro by going to Tool>Macro>Macros.
>>
>> You can also assign this macro to a button or a shortcut key combo.
>>
>> An alternative to running the macro manually is to run it whenever you hit the
>> Print command.
>>
>> Instead of pasting the code into a General Module, double-click on the
>> Thisworkbook module when in the VBE
>>
>> Paste this code into that module.............
>>
>> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>> With ActiveSheet
>> .PageSetup.CenterFooter = .Range("A1").Text
>> End With
>> End Sub
>>
>> Will run whenever you print.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Fri, 14 Jul 2006 08:05:02 -0700, Footers and Cell references
>> <(E-Mail Removed)> wrote:
>>
>> >Gordon,
>> >Thanks for the advise, having never used code before, I presume I have to go
>> >into
>> >Alt+Shift+F11.
>> >When I seach search for Footer I found the followng line. I have attempted
>> >to change it but the code does not appear to work. Can you advise what I am
>> >doing wrong please?
>> >
>> >{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
>> > \PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
>> >table
>> >
>> >Thanks
>> >Richard
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Sub CellInFooter()
>> >> With ActiveSheet
>> >> .PageSetup.CenterFooter = .Range("A1").text
>> >> End With
>> >> End Sub
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >>
>> >>
>> >> On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
>> >> Cell (E-Mail Removed)> wrote:
>> >>
>> >> >Attempting to print some reports from a Pivot Table and should like to use a
>> >> >cell value as a footer. Is it possible to quote a cell value in the Footers?
>> >>
>> >>
>>
>> Gord Dibben MS Excel MVP
>>

I have made the changes (I am obviously new to the VB arena) and will test on
his machine when possible.

His security has been reset to Medium.
I am not sure where to look to make sure he has not disengaged his macros
when the workbook opens.




"Gord Dibben" wrote:

> Couple of points...........
>
> 1. The Sub CellInFooter() goes into a standard or general module, not
> Thisworkbook module and is run by a button or shortcut key combo or
> Tools>Macro>Macros.
>
> 2. The BeforePrint code is event code and goes into the Thisworkbook module and
> will fire when you hit the "Print" button.
>
> Is the other user enabling macros when opening the workbook?
>
> Does the workbook open for user but can't use the macro or event code won't
> fire?
>
>
> Gord
>
> On Tue, 10 Apr 2007 13:26:01 -0700, smhish <(E-Mail Removed)>
> wrote:
>
> >Hi Gord!
> >
> >Found your reply in the Newsgroup. Great reply! It works great on my doc on
> >my machine, the problem is it won't work for anyone else without tweaking.
> >I saved the VB code told my user to test it...it failed. I have the the
> >Private Sub Workbook_BeforePrint and the Sub CellInFooter() in This Workbook.
> > Even changed the Sub to a Private Sub. Still no luck.
> >
> >The worksheet has a running total at cell W178. I can get that to appear.
> >But when he opens the file on his machine, it will not update the footer. I
> >checked the security and had it set to Medium. Nada.
> >
> >Any clues? Thanks for any ideas!
> >
> >
> >"Gord Dibben" wrote:
> >
> >> This is a macro to be run by hitting a button or shortcut key.
> >>
> >> If not familiar with VBA and macros, see David McRitchie's site for more on
> >> "getting started".
> >>
> >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >>
> >> In the meantime..........
> >>
> >> First...create a backup copy of your original workbook.
> >>
> >> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
> >>
> >> Hit CRTL + R to open Project Explorer.
> >>
> >> Find your workbook/project by name and select it.
> >>
> >> Right-click and Insert>Module. Paste the code in there. Save the
> >> workbook and hit ALT + Q to return to your workbook.
> >>
> >> Run the macro by going to Tool>Macro>Macros.
> >>
> >> You can also assign this macro to a button or a shortcut key combo.
> >>
> >> An alternative to running the macro manually is to run it whenever you hit the
> >> Print command.
> >>
> >> Instead of pasting the code into a General Module, double-click on the
> >> Thisworkbook module when in the VBE
> >>
> >> Paste this code into that module.............
> >>
> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >> With ActiveSheet
> >> .PageSetup.CenterFooter = .Range("A1").Text
> >> End With
> >> End Sub
> >>
> >> Will run whenever you print.
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Fri, 14 Jul 2006 08:05:02 -0700, Footers and Cell references
> >> <(E-Mail Removed)> wrote:
> >>
> >> >Gordon,
> >> >Thanks for the advise, having never used code before, I presume I have to go
> >> >into
> >> >Alt+Shift+F11.
> >> >When I seach search for Footer I found the followng line. I have attempted
> >> >to change it but the code does not appear to work. Can you advise what I am
> >> >doing wrong please?
> >> >
> >> >{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
> >> > \PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
> >> >table
> >> >
> >> >Thanks
> >> >Richard
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> Sub CellInFooter()
> >> >> With ActiveSheet
> >> >> .PageSetup.CenterFooter = .Range("A1").text
> >> >> End With
> >> >> End Sub
> >> >>
> >> >>
> >> >> Gord Dibben MS Excel MVP
> >> >>
> >> >>
> >> >>
> >> >> On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
> >> >> Cell (E-Mail Removed)> wrote:
> >> >>
> >> >> >Attempting to print some reports from a Pivot Table and should like to use a
> >> >> >cell value as a footer. Is it possible to quote a cell value in the Footers?
> >> >>
> >> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
>
>

With his Macro Security settings at Medium he will get the "this workbook
contains macros" message and the option to "enable" or "disable" macros.

Instruct him to "enable" macros.


Gord

On Wed, 11 Apr 2007 08:12:03 -0700, smhish <(E-Mail Removed)>
wrote:

>I have made the changes (I am obviously new to the VB arena) and will test on
>his machine when possible.
>
>His security has been reset to Medium.
>I am not sure where to look to make sure he has not disengaged his macros
>when the workbook opens.
>
>
>
>
>"Gord Dibben" wrote:
>
>> Couple of points...........
>>
>> 1. The Sub CellInFooter() goes into a standard or general module, not
>> Thisworkbook module and is run by a button or shortcut key combo or
>> Tools>Macro>Macros.
>>
>> 2. The BeforePrint code is event code and goes into the Thisworkbook module and
>> will fire when you hit the "Print" button.
>>
>> Is the other user enabling macros when opening the workbook?
>>
>> Does the workbook open for user but can't use the macro or event code won't
>> fire?
>>
>>
>> Gord
>>
>> On Tue, 10 Apr 2007 13:26:01 -0700, smhish <(E-Mail Removed)>
>> wrote:
>>
>> >Hi Gord!
>> >
>> >Found your reply in the Newsgroup. Great reply! It works great on my doc on
>> >my machine, the problem is it won't work for anyone else without tweaking.
>> >I saved the VB code told my user to test it...it failed. I have the the
>> >Private Sub Workbook_BeforePrint and the Sub CellInFooter() in This Workbook.
>> > Even changed the Sub to a Private Sub. Still no luck.
>> >
>> >The worksheet has a running total at cell W178. I can get that to appear.
>> >But when he opens the file on his machine, it will not update the footer. I
>> >checked the security and had it set to Medium. Nada.
>> >
>> >Any clues? Thanks for any ideas!
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> This is a macro to be run by hitting a button or shortcut key.
>> >>
>> >> If not familiar with VBA and macros, see David McRitchie's site for more on
>> >> "getting started".
>> >>
>> >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>> >>
>> >> In the meantime..........
>> >>
>> >> First...create a backup copy of your original workbook.
>> >>
>> >> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>> >>
>> >> Hit CRTL + R to open Project Explorer.
>> >>
>> >> Find your workbook/project by name and select it.
>> >>
>> >> Right-click and Insert>Module. Paste the code in there. Save the
>> >> workbook and hit ALT + Q to return to your workbook.
>> >>
>> >> Run the macro by going to Tool>Macro>Macros.
>> >>
>> >> You can also assign this macro to a button or a shortcut key combo.
>> >>
>> >> An alternative to running the macro manually is to run it whenever you hit the
>> >> Print command.
>> >>
>> >> Instead of pasting the code into a General Module, double-click on the
>> >> Thisworkbook module when in the VBE
>> >>
>> >> Paste this code into that module.............
>> >>
>> >> Private Sub Workbook_BeforePrint(Cancel As Boolean)
>> >> With ActiveSheet
>> >> .PageSetup.CenterFooter = .Range("A1").Text
>> >> End With
>> >> End Sub
>> >>
>> >> Will run whenever you print.
>> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>> >> On Fri, 14 Jul 2006 08:05:02 -0700, Footers and Cell references
>> >> <(E-Mail Removed)> wrote:
>> >>
>> >> >Gordon,
>> >> >Thanks for the advise, having never used code before, I presume I have to go
>> >> >into
>> >> >Alt+Shift+F11.
>> >> >When I seach search for Footer I found the followng line. I have attempted
>> >> >to change it but the code does not appear to work. Can you advise what I am
>> >> >doing wrong please?
>> >> >
>> >> >{mso-footer-data:"&LSub CellInFooter\(\)\000A With ActiveSheet\000A
>> >> > \PageSetup\CenterFooter = \Range("C5").text\ End Sub&C&D&RPage &P";}
>> >> >table
>> >> >
>> >> >Thanks
>> >> >Richard
>> >> >
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> Sub CellInFooter()
>> >> >> With ActiveSheet
>> >> >> .PageSetup.CenterFooter = .Range("A1").text
>> >> >> End With
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> Gord Dibben MS Excel MVP
>> >> >>
>> >> >>
>> >> >>
>> >> >> On Thu, 13 Jul 2006 09:03:02 -0700, Footers and Cell references <Footers and
>> >> >> Cell (E-Mail Removed)> wrote:
>> >> >>
>> >> >> >Attempting to print some reports from a Pivot Table and should like to use a
>> >> >> >cell value as a footer. Is it possible to quote a cell value in the Footers?
>> >> >>
>> >> >>
>> >>
>> >> Gord Dibben MS Excel MVP
>> >>
>>
>>