Print Area

cgrinage

New Member
Joined
Nov 10, 2011
Messages
4
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,121
Office Version
  1. 2013
Platform
  1. Windows
cgrinage,

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

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
@ cgrinage:

Maybe

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

In this line,

Code:
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

New Member
Joined
Nov 10, 2011
Messages
4
@ cgrinage:

Maybe

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

In this line,

Code:
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
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

cgrinage

New Member
Joined
Nov 10, 2011
Messages
4
cgrinage,

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

Code:
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

Well-known Member
Joined
May 17, 2009
Messages
5,121
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top