Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro formatting last row | Excel Worksheet Functions | |||
formatting macro help | Excel Discussion (Misc queries) | |||
Formatting using Macro | Excel Discussion (Misc queries) | |||
Formatting for a Macro | Excel Discussion (Misc queries) | |||
Formatting via a macro | Excel Discussion (Misc queries) |