Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro and sub rountines
I have a spreadsheet with 100 tabs, it is automatically created from another
program (Microsoft FRx Financial Statement program) I need to reformat each tab so that the report will print the way I want it to. I was able to create a macro that does what I want, but I don't know how to easily copy it so that it works universally for all tabs in the workbook. This is what I tried but it doesn't work and I need some help The problem is with Sub Macro2(), I don't know how to set up a nested macro so the macro will repeat itself for all tabs in the workbook Sub Macro2() ' ' Macro2 Macro ' Macro recorded 3/23/2005 by jdeeds ' ' Keyboard Shortcut: Ctrl+c ' Sheets("SUMMARY").Select Macro1() Sheets("SUMMARY").Name = "SUMMARY" Macro1() Sheets("SUMMARY - Acct Detail").Select Macro1() Sheets("SUMMARY - Acct Detail").Name = "SUMMARY - Acct Detail" Macro1() Sheets("000-xx").Select Macro1() End Sub Sub Macro1() ' ' Macro1 Macro ' Macro recorded 3/23/2005 by jdeeds ' ' Keyboard Shortcut: Ctrl+b ' With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.2) .RightMargin = Application.InchesToPoints(0.2) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.2) .FooterMargin = Application.InchesToPoints(0.2) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 85 End With End Sub |
#2
|
|||
|
|||
I haven't kept the code where you name the sheet to the same name that it
already has. Sub Macro2() Dim sh As Worksheet For Each sh In Activeworkbook.Worksheets Macro1 sh Next sh End Sub Sub Macro1(sh As Worksheet) With sh.PageSetup .PrintTitleRows = "$1:$7" .CenterFooter = "Page &P of &N" .LeftMargin = Application.InchesToPoints(0.2) .RightMargin = Application.InchesToPoints(0.2) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.2) .FooterMargin = Application.InchesToPoints(0.2) .PrintQuality = 600 .Orientation = xlLandscape .PaperSize = xlPaperLetter .Zoom = 85 End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "The Good Deeds Team" wrote in message ... I have a spreadsheet with 100 tabs, it is automatically created from another program (Microsoft FRx Financial Statement program) I need to reformat each tab so that the report will print the way I want it to. I was able to create a macro that does what I want, but I don't know how to easily copy it so that it works universally for all tabs in the workbook. This is what I tried but it doesn't work and I need some help The problem is with Sub Macro2(), I don't know how to set up a nested macro so the macro will repeat itself for all tabs in the workbook Sub Macro2() ' ' Macro2 Macro ' Macro recorded 3/23/2005 by jdeeds ' ' Keyboard Shortcut: Ctrl+c ' Sheets("SUMMARY").Select Macro1() Sheets("SUMMARY").Name = "SUMMARY" Macro1() Sheets("SUMMARY - Acct Detail").Select Macro1() Sheets("SUMMARY - Acct Detail").Name = "SUMMARY - Acct Detail" Macro1() Sheets("000-xx").Select Macro1() End Sub Sub Macro1() ' ' Macro1 Macro ' Macro recorded 3/23/2005 by jdeeds ' ' Keyboard Shortcut: Ctrl+b ' With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "Page &P of &N" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.2) .RightMargin = Application.InchesToPoints(0.2) .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.2) .FooterMargin = Application.InchesToPoints(0.2) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 85 End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|