Setting up Printing via a macro

[odayand]

Hi All

Can anybody assist?

I am new to macros so I have very little coding knowledge.

I am attempting to set up a printing macro for my workbook which has several sheets.

I have been searching the forums for something that would accomplish this to no avail. I did find this Macro which requires me to highlight the print area and then run it.

Dim myRange As String
     
Sub Printarea()
myRange = Selection.Address
ActiveSheet.PageSetup.PrintArea = myRange
With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.2)
    .FooterMargin = Application.InchesToPoints(0.2)
    .PaperSize = xlPaperA4
    .Orientation = xlPortrait 'xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=True 'Print Preview
'ActiveWindow.SelectedSheets.PrintOut Copies:=1 'Printout
End Sub
[\Code]
Is there something that will identify the used cells in each cell, set it as the print area, change the orientation to landscape, adjust the margins to 0.5mm all round shrink the columns to fit on one page wide.

Any assistance will be appreciated

Regards
Derryn

 

[AlphaFrog]

Is there something that will identify the used cells in each cell, set it as the print area, change the orientation to landscape, adjust the margins to 0.5mm all round shrink the columns to fit on one page wide.

Did you mean the used cells on the sheet?

All of those steps can be recorded to a macro. I suggest the 1st step to record is to clear the print area. Then the used range is the print area by default. All the other steps are on the Page Setup dialog and can be recorded.

 

[odayand]

Thanks for the reponse

Used the following and it worked

Dim W As Worksheet

For Each W In ActiveWorkbook.Worksheets
With W.PageSetup
.Printarea = W.UsedRange.Address
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.2)
.FooterMargin = Application.InchesToPoints(0.2)
.PaperSize = xlPaperA4
.Orientation = xlPortrait 'xlLandscape
.Zoom = False
.FitToPagesWide = 1
End With
W.PrintOut Copies:=1, Preview:=True 'Print Preview
'W.PrintOut Copies:=1 'Printout
Next W