Set print area for multiple sheets using VBA

[Snail123]

Hello,
I am very new to VBA and I am looking to create a macro to define the print area for each worksheet in my workbook.

Currently this is the code I am using and it is assigned to a button; the trouble I have is that instead of setting the print area to the last cell of each worksheet (the number of columns and rows on each are different), it only sets the print area to row 17, i.e. the last cell in Sheet 1 where the button is placed.

Is there a way where the button can stay on Sheet 1 but the print area is set to the last cell used (row and column with data) for Sheets 1 to 8?

Code:

Sub Print_area()
'Get values
Dim wks As Worksheet
Dim lastCell As Long
lastCell=Range("B" & Rows.Count).End(xlUp).Row
For Each wks in Worksheets
wks.PageSetup.PrintArea = "B1:J" & lastCell
Next wks

'Formatting
For Each wks In ThisWorkbook.Worksheets
With wks.PageSetup
.LeftHeader = "Test"
.CenterHeader = "Test"
.CenterHorizontally = True
.Orientation = xlLandscape
.PaperSize = xlPaperA2
.FitToPagesWide = 1
End With
Next wks
End Sub

Thanks for your help (in anticipation!)

PS. I'm using MS Excel 2013

 

[Momentman]

Try this way

Sub Print_area()
    'Get values
    Dim wks As Worksheet
    Dim lastCell As Long
    
    For Each wks In ActiveWorkbook.Worksheets
        lastCell = wks.Range("B" & Rows.Count).End(xlUp).Row
        wks.PageSetup.PrintArea = "B1:J" & lastCell
        With wks.PageSetup
            .LeftHeader = "Test"
            .CenterHeader = "Test"
            .CenterHorizontally = True
            .Orientation = xlLandscape
            .PaperSize = xlPaperA2
            .FitToPagesWide = 1
        End With
    Next wks
End Sub

 

[Snail123]

Thanks for your help!

 

[Momentman]

Thanks for your help!

I should take it that it works

 

[Snail123]

Yes perfectly!

 

[Ashwani27]

Hello,
I am very new to VBA and I am looking to create a macro to define the print area for each worksheet in my workbook.

Currently this is the code I am using and it is assigned to a button; the trouble I have is that instead of setting the print area to the last cell of each worksheet (the number of columns and rows on each are different), it only sets the print area to row 17, i.e. the last cell in Sheet 1 where the button is placed.

Is there a way where the button can stay on Sheet 1 but the print area is set to the last cell used (row and column with data) for Sheets 1 to 8?

Code:

Sub Print_area()
'Get values
Dim wks As Worksheet
Dim lastCell As Long
lastCell=Range("B" & Rows.Count).End(xlUp).Row
For Each wks in Worksheets
wks.PageSetup.PrintArea = "B1:J" & lastCell
Next wks

'Formatting
For Each wks In ThisWorkbook.Worksheets
With wks.PageSetup
.LeftHeader = "Test"
.CenterHeader = "Test"
.CenterHorizontally = True
.Orientation = xlLandscape
.PaperSize = xlPaperA2
.FitToPagesWide = 1
End With
Next wks
End Sub

Thanks for your help (in anticipation!)

PS. I'm using MS Excel 2013

Hi Momentum,

Thanks for this wonderful help, but could you please help if I want to select print ranges in selected work sheets (For Eg: Sheet 2,3,4 only), not all work sheets and in addition till the value in column B in the mention 3 sheets greater than 0 not last cell.