Is there a way to copy a header into all worksheets in a file automatically

You can select all tabs and then set header information...

Otherwise you probably need to use a macro... unless there is a way which I
am not aware of.

"Laura" wrote:

Is there a way to copy a header into all worksheets in a file automatically

Laura

If I already had the header/footer set on one worksheet, in pre xl2007 (it
may work on 2007 I just don't speak 07 yet) I would "Group" all sheets with
the one with the header/footer as the ative one. I would goto viewheader and
footerI would select "ok" and let xl do the rest. It will copy the
header/footer to all worksheets. As Sheleoo said you can also modify, or
build from scratch, the header/footer at this time and it will hit all
worksheets.

Mike Rogers

"Laura" wrote:

Is there a way to copy a header into all worksheets in a file automatically

One caveat with this method.

ALL print settings except for print range and rows to repeat at top will be
changed to that of the activesheet.

So, if you wish to maintain different margins or other sheet settings for
each sheet, do not use this method.


Gord Dibben MS Excel MVP

On Wed, 4 Mar 2009 08:14:04 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

Laura

If I already had the header/footer set on one worksheet, in pre xl2007 (it
may work on 2007 I just don't speak 07 yet) I would "Group" all sheets with
the one with the header/footer as the ative one. I would goto viewheader and
footerI would select "ok" and let xl do the rest. It will copy the
header/footer to all worksheets. As Sheleoo said you can also modify, or
build from scratch, the header/footer at this time and it will hit all
worksheets.

Mike Rogers

"Laura" wrote:

Is there a way to copy a header into all worksheets in a file automatically

Gord

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!

The one commodity that we have in the greatest abundance is ignorance, and I
wonder if we will ever get different, more abundant, commodity.

Thanks again

Mike Rogers

"Gord Dibben" wrote:

One caveat with this method.

ALL print settings except for print range and rows to repeat at top will be
changed to that of the activesheet.

So, if you wish to maintain different margins or other sheet settings for
each sheet, do not use this method.


Gord Dibben MS Excel MVP

On Wed, 4 Mar 2009 08:14:04 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

Laura

If I already had the header/footer set on one worksheet, in pre xl2007 (it
may work on 2007 I just don't speak 07 yet) I would "Group" all sheets with
the one with the header/footer as the ative one. I would goto viewheader and
footerI would select "ok" and let xl do the rest. It will copy the
header/footer to all worksheets. As Sheleoo said you can also modify, or
build from scratch, the header/footer at this time and it will hit all
worksheets.

Mike Rogers

"Laura" wrote:

Is there a way to copy a header into all worksheets in a file automatically

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!

Gord,

I currently use a macro I wrote to apply a common footer to ALL worksheets
in the currently active workbook. It's a very basic For:Next code for each
WS. The problem is, it takes 5-6 seconds per worksheet to actually run the
code. So, my larger workbooks that have 50+ worksheets take several minutes
to run the footer code.

Is there a different code to select all the sheets in the active workbook,
apply the footer, and do so more quickly than my current? Also, I'm a novice
with VBA, but actively learning.

Thanks!

Tondos

"Gord Dibben" wrote:

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!

Any print or page setup on all sheets takes time depending upon the
complexity of the setup.

Remember............you're working with a printer driver.

I can't see your code but if all you need is common sheet print setups I
would suggest just grouping the sheets then adding the footer manually.

If each sheet needs its own print settings then you must use the macro to
just set the footer.

Post the code..............may be ways to speed it up.


Gord


On Mon, 16 Mar 2009 13:41:04 -0700, Tondos
wrote:

Gord,

I currently use a macro I wrote to apply a common footer to ALL worksheets
in the currently active workbook. It's a very basic For:Next code for each
WS. The problem is, it takes 5-6 seconds per worksheet to actually run the
code. So, my larger workbooks that have 50+ worksheets take several minutes
to run the footer code.

Is there a different code to select all the sheets in the active workbook,
apply the footer, and do so more quickly than my current? Also, I'm a novice
with VBA, but actively learning.

Thanks!

Tondos

"Gord Dibben" wrote:

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!

Thanks, Gord.

I've had several attempts at creating this code, but I could only find this
one in my Personal workbook. I think this was one of my earlier, recorded
macros from 2 years ago.

Anyway, print range would definitely be unique to each sheet, but footer and
margins, etc. would be identical for all sheets.

The macro was supposed to save the time of selecting all the sheets and
needing to manually update the footer info. So the macro has saved the
'manual entry time' but caused a long 'waiting time' while the code executes!

Here is the code:

Sub FilePathFooterOrig()

'Formats Standardized Footer & Saves

Dim wSheet As Worksheet

For Each wSheet In Worksheets

With wSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&8& My Company Name " & Chr(10) & "&8&D &T" & Chr(10)
& Application.UserName
.CenterFooter = "&8&P/&N"
.RightFooter = "&8&Z" & Chr(10) & "&8&F" & Chr(10) & "&8&A"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Next wSheet

ActiveWorkbook.Save

End Sub

Any help is greatly appreciated!

Tondos

"Gord Dibben" wrote:

Any print or page setup on all sheets takes time depending upon the
complexity of the setup.

Remember............you're working with a printer driver.

I can't see your code but if all you need is common sheet print setups I
would suggest just grouping the sheets then adding the footer manually.

If each sheet needs its own print settings then you must use the macro to
just set the footer.

Post the code..............may be ways to speed it up.


Gord


On Mon, 16 Mar 2009 13:41:04 -0700, Tondos
wrote:

Gord,

I currently use a macro I wrote to apply a common footer to ALL worksheets
in the currently active workbook. It's a very basic For:Next code for each
WS. The problem is, it takes 5-6 seconds per worksheet to actually run the
code. So, my larger workbooks that have 50+ worksheets take several minutes
to run the footer code.

Is there a different code to select all the sheets in the active workbook,
apply the footer, and do so more quickly than my current? Also, I'm a novice
with VBA, but actively learning.

Thanks!

Tondos

"Gord Dibben" wrote:

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!

You could add a couple of things to help speed up but not by much.

Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wSheet In Worksheets
yada yada yada
Next wSheet
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.Save

It has been my experience that page and print setups are very slow.

With 50 sheets a few minutes is to be expected.

Perhaps others have some ideas. He says hopefully<g


Gord

On Mon, 16 Mar 2009 15:11:02 -0700, Tondos
wrote:

Thanks, Gord.

I've had several attempts at creating this code, but I could only find this
one in my Personal workbook. I think this was one of my earlier, recorded
macros from 2 years ago.

Anyway, print range would definitely be unique to each sheet, but footer and
margins, etc. would be identical for all sheets.

The macro was supposed to save the time of selecting all the sheets and
needing to manually update the footer info. So the macro has saved the
'manual entry time' but caused a long 'waiting time' while the code executes!

Here is the code:

Sub FilePathFooterOrig()

'Formats Standardized Footer & Saves

Dim wSheet As Worksheet

For Each wSheet In Worksheets

With wSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&8& My Company Name " & Chr(10) & "&8&D &T" & Chr(10)
& Application.UserName
.CenterFooter = "&8&P/&N"
.RightFooter = "&8&Z" & Chr(10) & "&8&F" & Chr(10) & "&8&A"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Next wSheet

ActiveWorkbook.Save

End Sub

Any help is greatly appreciated!

Tondos

"Gord Dibben" wrote:

Any print or page setup on all sheets takes time depending upon the
complexity of the setup.

Remember............you're working with a printer driver.

I can't see your code but if all you need is common sheet print setups I
would suggest just grouping the sheets then adding the footer manually.

If each sheet needs its own print settings then you must use the macro to
just set the footer.

Post the code..............may be ways to speed it up.


Gord


On Mon, 16 Mar 2009 13:41:04 -0700, Tondos
wrote:

Gord,

I currently use a macro I wrote to apply a common footer to ALL worksheets
in the currently active workbook. It's a very basic For:Next code for each
WS. The problem is, it takes 5-6 seconds per worksheet to actually run the
code. So, my larger workbooks that have 50+ worksheets take several minutes
to run the footer code.

Is there a different code to select all the sheets in the active workbook,
apply the footer, and do so more quickly than my current? Also, I'm a novice
with VBA, but actively learning.

Thanks!

Tondos

"Gord Dibben" wrote:

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!

Thanks, Gord.

"Gord Dibben" wrote:

You could add a couple of things to help speed up but not by much.

Dim wSheet As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wSheet In Worksheets
yada yada yada
Next wSheet
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.Save

It has been my experience that page and print setups are very slow.

With 50 sheets a few minutes is to be expected.

Perhaps others have some ideas. He says hopefully<g


Gord

On Mon, 16 Mar 2009 15:11:02 -0700, Tondos
wrote:

Thanks, Gord.

I've had several attempts at creating this code, but I could only find this
one in my Personal workbook. I think this was one of my earlier, recorded
macros from 2 years ago.

Anyway, print range would definitely be unique to each sheet, but footer and
margins, etc. would be identical for all sheets.

The macro was supposed to save the time of selecting all the sheets and
needing to manually update the footer info. So the macro has saved the
'manual entry time' but caused a long 'waiting time' while the code executes!

Here is the code:

Sub FilePathFooterOrig()

'Formats Standardized Footer & Saves

Dim wSheet As Worksheet

For Each wSheet In Worksheets

With wSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&8& My Company Name " & Chr(10) & "&8&D &T" & Chr(10)
& Application.UserName
.CenterFooter = "&8&P/&N"
.RightFooter = "&8&Z" & Chr(10) & "&8&F" & Chr(10) & "&8&A"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

Next wSheet

ActiveWorkbook.Save

End Sub

Any help is greatly appreciated!

Tondos

"Gord Dibben" wrote:

Any print or page setup on all sheets takes time depending upon the
complexity of the setup.

Remember............you're working with a printer driver.

I can't see your code but if all you need is common sheet print setups I
would suggest just grouping the sheets then adding the footer manually.

If each sheet needs its own print settings then you must use the macro to
just set the footer.

Post the code..............may be ways to speed it up.


Gord


On Mon, 16 Mar 2009 13:41:04 -0700, Tondos
wrote:

Gord,

I currently use a macro I wrote to apply a common footer to ALL worksheets
in the currently active workbook. It's a very basic For:Next code for each
WS. The problem is, it takes 5-6 seconds per worksheet to actually run the
code. So, my larger workbooks that have 50+ worksheets take several minutes
to run the footer code.

Is there a different code to select all the sheets in the active workbook,
apply the footer, and do so more quickly than my current? Also, I'm a novice
with VBA, but actively learning.

Thanks!

Tondos

"Gord Dibben" wrote:

Thanks for the feedback and kind words.

Made the mistake a time or two myself.

Get everything set nicely for individual sheets then decided I wanted a
common Footer.

Ooops!


Gord

On Wed, 4 Mar 2009 14:51:03 -0800, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom wrote:

I knew there was a reason you were always watching this site. <bg Thanks
for the bump in to right direction, I did not realize all the print settings
that this would effect!!!!