Print sub returns

M

Montana

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
 
D

Don Guillett

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
 
M

Montana

Worked great!

Thanks
--
Peace


Don Guillett said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print area - selection 4
Printing Setup help 9
page setup preferances 2
Setting Print Range 2
Page setup error after moving sheet tabs 3
Define a Variable 1
Printing-don't need half this code but which half?:-) 5
Print Routine Error 4

Top