Printing-don't need half this code but which half?:-)

[TD]

Hi,

I've recorded a macro for printing pre-selected ranges. The spreadsheet is
going out to lots of users who will all have different ways of setting the
print range - some more successful than others. So I thought I would put a
button in so that everyone would in theory get the same printout.

I'm sure there's lots in here that isn't needed just don't know what.

This is a just part of the whole code (prints 11 sheets with varying areas-
there are 15 sheets in book).
Sheets("Data1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data2").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "Data2"
.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
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

I also had a thought that someone here would be able to tell me how to cut
down on some of the code I'm sure I need to do With Selection and Endwith but
not sure where and what so that properties common to all printing is only
coded once.

I'm at the record macro and muddle about stage but learning all the time so
it would be great is someone could work out what I mean and then help.

Many thanks

 

[Susan]

i don't know exactly what parts of the page set-up code you don't
need, because i can't tell all of what you've changed. but one thing
i would do, if i were you, to save space, is to put that page set-up
code in a separate macro sub & then just refer to it, instead of
spelling it out every time:
===========================
sub set_up_page()

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.748031496062992)
.RightMargin = Application.InchesToPoints(0.748031496062992)
.TopMargin = Application.InchesToPoints(0.984251968503937)
.BottomMargin = Application.InchesToPoints(0.984251968503937)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

end sub


sub TD()

Sheets("Data1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15"

Call set_up_page

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data2").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21"

Call set_up_page

'etc, etc, etc

end sub
======================

of course, this will only work if your set-up sub is the same each
time, which it looks like to me.
hope this helps

susan

 

[Jim Thomlinson]

If you want to ensure that the printouts are absolutely identical then you
need all of the settings. While it is unlikely that anyone is going to play
with 90% of those settings the only way to ensure 100% it to define all of
the settings. If however all you want to define is the print area then there
is not much to that...

With Sheets("Data1")
.PageSetup.PrintArea = "$A$1:$F$21"
.PrintPreview
.PrintOut
end with
With Sheets("Data2")
.PageSetup.PrintArea = "$A$1:$F$15"
.PrintPreview
.PrintOut
end with

Note that for all of the other settings whatever the user set them to last
is the value that will be used. So if they changed margins or headers or such
then that is what they will get.

 

[Jim Thomlinson]

Sorry you can comment out the printpreview. I added that in for debugging. I
try not to kill too many trees when I am trying to get the printing to work
out...

 

[TD]

Thank you Susan,

Will look at this later - onto something else at the mo' but will try this
asap.

Thanks again

 

[TD]

Thanks Jim,

Busy with another bit of work but will try asap.

I'm with you on the trees bit

Thank you again