Set Print area via Macros

Hi Sam,

Thank you for the forum question. I hope the Excel day was useful.


This cannot be done with the macro recorder but with VBA.

You will need to loop through the worksheets with a For Each loop.

Please test the code below in a copy of the workbook. In the copy open the visual basic editor (Alt F11). In the Project Explorer (left top) right click on the name of the workbook and click Insert Module. Copy the code below and paste it in the module. Run the code.

I have created the code from how I remember your worksheets. Please let me know how it goes. I may need to make some adjustments. If it is working just move it to the right workbook.

Sub SetPrintAreaMulti()
Dim wks As Worksheet
Dim lastCell As Long
Dim LastCol As Variant

For Each wks In ActiveWorkbook.Worksheets
lastCell = wks.Range("c" & Rows.Count).End(xlUp).Row
LastCol = wks.Cells(5, Columns.Count).End(xlToLeft).Column

wks.PageSetup.PrintArea = "a1:" & ColLtr(LastCol) & lastCell + 1
With wks.PageSetup
.CenterHorizontally = True
.Orientation = xlPortrait

.FitToPagesWide = 1
End With
Next wks

End Sub

Function ColLtr(iCol As Variant) As String
If iCol > 0 And iCol <= Columns.Count Then ColLtr = Replace(Cells(1, iCol + 1).Address(0, 0), 1, "")
End Function








Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector