Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print sub returns
PRINT1 WORKS FINE; PRINT2 RETURNS A RUN-TIME 1004 ERROR. EACH SUB REFERS TO A
DIFFERENT WORKSHEET IN A WORKBOOK. Sub Print1() ' ' ' Print Macro ' Macro written 6/28/2006 by djones ' Copied here 10/12/2006 Worksheets("Summary").Activate Range("A1:AA22").Select ActiveSheet.PageSetup.PrintArea = "A1:AA22" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("A:B").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 3 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub Sub Print2() Worksheets("Assumptions").Activate Range("A1:D33").Select ActiveSheet.PageSetup.PrintArea = "A1:D33" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("a:b").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 1 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub -- Peace |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print sub returns
Solution worked great!
Thanks -- Peace "Montana" wrote: PRINT1 WORKS FINE; PRINT2 RETURNS A RUN-TIME 1004 ERROR. EACH SUB REFERS TO A DIFFERENT WORKSHEET IN A WORKBOOK. Sub Print1() ' ' ' Print Macro ' Macro written 6/28/2006 by djones ' Copied here 10/12/2006 Worksheets("Summary").Activate Range("A1:AA22").Select ActiveSheet.PageSetup.PrintArea = "A1:AA22" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("A:B").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 3 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub Sub Print2() Worksheets("Assumptions").Activate Range("A1:D33").Select ActiveSheet.PageSetup.PrintArea = "A1:D33" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("a:b").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 1 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub -- Peace |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print sub returns
Worked great!
Thanks -- Peace "Don Guillett" wrote: I tested sub 2, as written, and it worked just fine. However, you don't need selections. This will work from anywhere in the workbook. This type macro is very slow so I strongly suggest you eliminate any unnecessary lines in the code below or use another macro to JUST set up the constants and do it ONCE instead of each time. Sub Print2() With Worksheets("sheet8").PageSetup .PrintArea = "A1:D33" .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("a:b").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 1 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("sheet8").PrintOut End Sub -- Don Guillett SalesAid Software "Montana" wrote in message ... PRINT1 WORKS FINE; PRINT2 RETURNS A RUN-TIME 1004 ERROR. EACH SUB REFERS TO A DIFFERENT WORKSHEET IN A WORKBOOK. Sub Print1() ' ' ' Print Macro ' Macro written 6/28/2006 by djones ' Copied here 10/12/2006 Worksheets("Summary").Activate Range("A1:AA22").Select ActiveSheet.PageSetup.PrintArea = "A1:AA22" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("A:B").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 3 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub Sub Print2() Worksheets("Assumptions").Activate Range("A1:D33").Select ActiveSheet.PageSetup.PrintArea = "A1:D33" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("a:b").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 1 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub -- Peace |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print sub returns
glad to help but I don't know why your original didn't work.
-- Don Guillett SalesAid Software "Montana" wrote in message ... Worked great! Thanks -- Peace "Don Guillett" wrote: I tested sub 2, as written, and it worked just fine. However, you don't need selections. This will work from anywhere in the workbook. This type macro is very slow so I strongly suggest you eliminate any unnecessary lines in the code below or use another macro to JUST set up the constants and do it ONCE instead of each time. Sub Print2() With Worksheets("sheet8").PageSetup .PrintArea = "A1:D33" .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("a:b").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 1 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("sheet8").PrintOut End Sub -- Don Guillett SalesAid Software "Montana" wrote in message ... PRINT1 WORKS FINE; PRINT2 RETURNS A RUN-TIME 1004 ERROR. EACH SUB REFERS TO A DIFFERENT WORKSHEET IN A WORKBOOK. Sub Print1() ' ' ' Print Macro ' Macro written 6/28/2006 by djones ' Copied here 10/12/2006 Worksheets("Summary").Activate Range("A1:AA22").Select ActiveSheet.PageSetup.PrintArea = "A1:AA22" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("A:B").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 3 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub Sub Print2() Worksheets("Assumptions").Activate Range("A1:D33").Select ActiveSheet.PageSetup.PrintArea = "A1:D33" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "TCM EOM 07 Summary" .RightHeader = "" .LeftFooter = "&D" .CenterFooter = "Page &P" .FirstPageNumber = "1" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintTitleColumns = ActiveSheet.Columns("a:b").Address .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLegal .FitToPagesTall = 1 .FitToPagesWide = 1 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With ' ActiveWindow.SelectedSheets.PrintOut Copies:=1 ActiveWindow.SelectedSheets.PrintPreview End Sub -- Peace |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index with mulitple value returns and muliple column returns | Excel Worksheet Functions | |||
Date returns always returns: 00 January 1900 | Excel Worksheet Functions | |||
cell with value returns that value, empty cell returns zero | Excel Worksheet Functions | |||
Pivot Table macro to set print area and print details of drill down data | Excel Discussion (Misc queries) | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming |