Hi all! I know it will only take seconds for you to help me out!! :)

I have a workbook containing quite a few worksheets. On each worksheet
I have table that are the same format etc.

I want to setup a button to print the various reports on eac
worksheets.

I have setup a module which contains the formating required once Prin
area has been set.

I have created a button with a Command_click that sets the Print Are
for each reports, then prints.

My issue is in mixing the two!!


Basically I have:

Private Sub CommandButton1_Click()

'####### Report1 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$4:$Z$23"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'####### Report2 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$31:$Z$50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'####### Report3 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$58:$Z$77"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

etc....

and a Format sub as follow:

Private Sub Format()
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 = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

Now how can I call the formatting sub each time i set the new prin
area??

Many thanks for your help!

PS - I'm sure there ARE better ways to achieve what i want... let m
know! :

--
Petitboeu
-----------------------------------------------------------------------
Petitboeuf's Profile: http://www.excelforum.com/member.php...fo&userid=1060
View this thread: http://www.excelforum.com/showthread.php?threadid=47679

I renamed you format sub to Myformat since format is a built in command.

You only need to call myformat once since it does nothing that is particular
to the print area.

Private Sub CommandButton1_Click()
MyFormat
'####### Report1 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$4:$Z$23"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'####### Report2 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$31:$Z$50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'####### Report3 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$58:$Z$77"
ActiveWindow.SelectedSheets.PrintOut Copies:=1


Private Sub MyFormat()
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 = xlLandscape
Draft = False
PaperSize = xlPaperLetter
FirstPageNumber = xlAutomatic
Order = xlDownThenOver
BlackAndWhite = False
Zoom = False
FitToPagesWide = 1
FitToPagesTall = 1
PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

"Petitboeuf" wrote
in message ...



Hi all! I know it will only take seconds for you to help me out!! :)

I have a workbook containing quite a few worksheets. On each worksheets
I have table that are the same format etc.

I want to setup a button to print the various reports on each
worksheets.

I have setup a module which contains the formating required once Print
area has been set.

I have created a button with a Command_click that sets the Print Area
for each reports, then prints.

My issue is in mixing the two!!


Basically I have:

Private Sub CommandButton1_Click()

'####### Report1 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$4:$Z$23"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'####### Report2 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$31:$Z$50"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

'####### Report3 PRINT #########
ActiveSheet.PageSetup.PrintArea = "$A$58:$Z$77"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

etc....

and a Format sub as follow:

Private Sub Format()
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 = xlLandscape
Draft = False
PaperSize = xlPaperLetter
FirstPageNumber = xlAutomatic
Order = xlDownThenOver
BlackAndWhite = False
Zoom = False
FitToPagesWide = 1
FitToPagesTall = 1
PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

Now how can I call the formatting sub each time i set the new print
area??

Many thanks for your help!

PS - I'm sure there ARE better ways to achieve what i want... let me
know! :)


--
Petitboeuf
------------------------------------------------------------------------
Petitboeuf's Profile:
http://www.excelforum.com/member.php...o&userid=10602
View this thread: http://www.excelforum.com/showthread...hreadid=476797