Print Area

[cgrinage]

I have several sheets (some Pivot tables) in a 2007 Workbook and would like to set the print area to the last row (last cell) of each sheet. The sheets are all different sizes. So far I have:

Sub test_Print_Area()
Dim c As Long, Printa As Range

c = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
Set Printa = ActiveSheet.Range(Cells(1, 1), Cells(Rows.Count, c).End(xlUp))

ActiveSheet.PageSetup.PrintArea = Printa.Address

End Sub


This works for one sheet and I need to for all the sheets in the book. I think I can use " For each" statement but I can't get it to work.

Help would be appreciated. Thanks

 

[Snakehips]

cgrinage,

Unless for some reason it doesn't like pivot tables this should work..

Sub SetPrinta ()

Dim c As Long, Printa As Range

For Each sht In ThisWorkbook.Sheets
c = sht.Cells(2, Columns.Count).End(xlToLeft).Column
  Set Printa = sht.Range(Cells(1, 1), Cells(Rows.Count, c).End(xlUp))
    sht.PageSetup.PrintArea = Printa.Address
Next sht

End Sub

Hope that helps?

 

[shg]

@ cgrinage:

Maybe

Sub x()
    Dim wks As Worksheet
 
    For Each wks In Worksheets
        wks.PageSetup.PrintArea = vbNullString
    Next wks
End Sub

@Snakehips:

In this line,

Set Printa = sht.Range([COLOR=red]Cells(1, 1)[/COLOR], [COLOR=red]Cells(Rows.Count, c)[/COLOR].End(xlUp))

... the highlighted references are to cells on the active sheet, not to cells on sht. Also, the code will attempt to set the print area on any chart sheets, which will fail.

 

[cgrinage]

@ cgrinage:

Maybe

Sub x()
    Dim wks As Worksheet
 
    For Each wks In Worksheets
        wks.PageSetup.PrintArea = vbNullString
    Next wks
End Sub

@Snakehips:

In this line,

Set Printa = sht.Range([COLOR=red]Cells(1, 1)[/COLOR], [COLOR=red]Cells(Rows.Count, c)[/COLOR].End(xlUp))

... the highlighted references are to cells on the active sheet, not to cells on sht. Also, the code will attempt to set the print area on any chart sheets, which will fail.

Not sure how the above code will help. Understand your explanation about

Set Printa = sht.Range([COLOR=red]Cells(1, 1)[/COLOR], [COLOR=red]Cells(Rows.Count, c)[/COLOR].End(xlUp))

... the highlighted references are to cells on the active sheet, not to cells on sht. Also, the code will attempt to set the print area on any chart sheets, which will fail.[/QUOTE]

 

[shg]

When you reset the print area, A1 to the end of the used range of the sheet will print.

 

[cgrinage]

cgrinage,

Unless for some reason it doesn't like pivot tables this should work..

Sub SetPrinta ()

Dim c As Long, Printa As Range

For Each sht In ThisWorkbook.Sheets
c = sht.Cells(2, Columns.Count).End(xlToLeft).Column
  Set Printa = sht.Range(Cells(1, 1), Cells(Rows.Count, c).End(xlUp))
    sht.PageSetup.PrintArea = Printa.Address
Next sht

End Sub

Hope that helps?

snakehip,

I tried the code and got an error in the "Set Printa" area object defined error.

 

[Snakehips]

cgrinage.

I originally just copied your supposed working code and adapted it for the each sht loop.

I notice now that there is a comma in the Set Printa line which should be a Point!!!!

See if that does the trick.