Macro for formatting
 

I have a worksheet with 100 tabs, and I need to apply the same formatting
to each one. I am trying to write a macro which will do this (one tab at a
time). Most of the following works OK, but

.FitToPagesWide = 1
.FitToPagesTall = 12

is ignored. I can't understand why.

Sub HeaderFooter()
'
' HeaderFooter Macro
' Macro recorded 23/07/2003 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$24:$26"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Operation Anuric"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
Application.ScreenUpdating = True
End Sub

However, the lines work when I run THIS macro:

Sub test()
'
' test Macro
' Macro recorded 05/09/2003 by bclarke
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
End Sub

Macro for formatting
 

Brian,

If you are using the FitToPages properties, first set the Zoom setting to
False.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Brian Clarke" wrote in message
...
I have a worksheet with 100 tabs, and I need to apply the same formatting
to each one. I am trying to write a macro which will do this (one tab at a
time). Most of the following works OK, but

.FitToPagesWide = 1
.FitToPagesTall = 12

is ignored. I can't understand why.

Sub HeaderFooter()
'
' HeaderFooter Macro
' Macro recorded 23/07/2003 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$24:$26"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Operation Anuric"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
Application.ScreenUpdating = True
End Sub

However, the lines work when I run THIS macro:

Sub test()
'
' test Macro
' Macro recorded 05/09/2003 by bclarke
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
End Sub

Macro for formatting
 

The Zoom setting will override the FitToPagesWide/Tall
settings, regardless of their values. This is like
choosing the 'Adjust to' setting on the page setup dialog.
When you set Zoom = False you go to 'Fit to' mode.

Each mode is mutualy exclusive

Cheers,
Dave
-----Original Message-----
I have a worksheet with 100 tabs, and I need to apply
the same formatting
to each one. I am trying to write a macro which will do
this (one tab at a
time). Most of the following works OK, but

.FitToPagesWide = 1
.FitToPagesTall = 12

is ignored. I can't understand why.

Sub HeaderFooter()
'
' HeaderFooter Macro
' Macro recorded 23/07/2003 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$24:$26"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Operation Anuric"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
Application.ScreenUpdating = True
End Sub

However, the lines work when I run THIS macro:

Sub test()
'
' test Macro
' Macro recorded 05/09/2003 by bclarke
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
End Sub




.

Macro for formatting
 

The other Non VBA solution is to simply group them all (Right click on a tab and select 'Select
All sheets'), apply your formatting to one and then ungroup them (Right click on a tab and select
'Ungroup sheets')

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Brian Clarke" wrote in message
...
I have a worksheet with 100 tabs, and I need to apply the same formatting
to each one. I am trying to write a macro which will do this (one tab at a
time). Most of the following works OK, but

.FitToPagesWide = 1
.FitToPagesTall = 12

is ignored. I can't understand why.

Sub HeaderFooter()
'
' HeaderFooter Macro
' Macro recorded 23/07/2003 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$24:$26"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Operation Anuric"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
Application.ScreenUpdating = True
End Sub

However, the lines work when I run THIS macro:

Sub test()
'
' test Macro
' Macro recorded 05/09/2003 by bclarke
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
End Sub

Macro for formatting
 

Many thanks to everyone for the fast advice.

Brian


"Chip Pearson" wrote in message
...
Brian,

If you are using the FitToPages properties, first set the Zoom setting to
False.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com


"Brian Clarke" wrote in message
...
I have a worksheet with 100 tabs, and I need to apply the same
formatting
to each one. I am trying to write a macro which will do this (one tab at
a
time). Most of the following works OK, but

.FitToPagesWide = 1
.FitToPagesTall = 12

is ignored. I can't understand why.

Sub HeaderFooter()
'
' HeaderFooter Macro
' Macro recorded 23/07/2003 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$24:$26"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Operation Anuric"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
Application.ScreenUpdating = True
End Sub

However, the lines work when I run THIS macro:

Sub test()
'
' test Macro
' Macro recorded 05/09/2003 by bclarke
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
End Sub

Macro for formatting
 

I found that solution does not work 100%. Part of the formatting I want is
to specify printing rows 24-26 at the top of every page. If I select a
group of sheets and try to do that, the option is one which is greyed out.


"Ken Wright" wrote in message
...
The other Non VBA solution is to simply group them all (Right click on a
tab and select 'Select
All sheets'), apply your formatting to one and then ungroup them (Right
click on a tab and select
'Ungroup sheets')

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------------------------
--
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------------------------
--



"Brian Clarke" wrote in message
...
I have a worksheet with 100 tabs, and I need to apply the same
formatting
to each one. I am trying to write a macro which will do this (one tab at
a
time). Most of the following works OK, but

.FitToPagesWide = 1
.FitToPagesTall = 12

is ignored. I can't understand why.

Sub HeaderFooter()
'
' HeaderFooter Macro
' Macro recorded 23/07/2003 by bclarke
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$24:$26"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "Operation Anuric"
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = "&D"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
Application.ScreenUpdating = True
End Sub

However, the lines work when I run THIS macro:

Sub test()
'
' test Macro
' Macro recorded 05/09/2003 by bclarke
'

'
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 12
End With
End Sub