Kent,

1)
To set the print area to just the pivot table, use code like

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PageSetup.PrintArea = Selection.Address

2)

Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Set myR = Selection

Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1, Columns.Count)).EntireColumn.Delete


If the sheet isn't the activesheet, use Worksheets("Sheetname").Activate first, in either example.
And, of course, you will need to change the name of the pivottable. If each sheet has only one, then

ActiveSheet.PivotTables(1).PivotSelect "", xlDataAndLabel, True

should work.

HTH,
Bernie
MS Excel MVP


"klysell" wrote in message
...
Hi,

My macro parses codes on my summary sheet into separate pivot tables
contained on separate worksheets thereby using these individual codes as page
fields for these pivot tables. Because I have too much data, I had to create
a forumula driven variance column lying outside of the pivot table (else the
code that creates the pivot table crashes). However, now my print macros
don't work because for some reason, the print preview function reads all the
variance formulas (although blank) going to down to, say, row 2000. Instead
of nicely fitting the pivot table onto one, two or three pages, I have 65+
blank pages due to the formulas running down past my pivot table (these
variance formulas show a blank i.e. "" in cells below my pivot tables).

Therefore, I either: 1) need to create a print area for just the pivot
table; or 2) delete all rows beneath the macro-generated pivot table
(starting row not known in advance).

Any help would be appreciate.

Cheers,

Kent Lysell
IBM Consultant
Ottawa, Ontario