Bernie,
I tried to second method to delete rows below my macro-generate pivot table
and got an error message "Method 'Range' of Object '_Worksheet' failed". Here
is the code that you gave me in the context of my code:
Sheets("PIV_SOF").Visible = True
Application.Goto "SOF_BACK_TO_SUMMARY"
Worksheets("PIV_SOF").Copy _
After:=Worksheets(Worksheets.Count)
Sheets("PIV_SOF").Visible = False
Set Sh = ActiveSheet
Sh.Name = Target & "-Source of Funds"
Sh.Tab.ColorIndex = 33
For Each pt In Sh.PivotTables
With pt
With .PivotFields("RC")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
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
Call Formatting_SOF
'
'
'
'
Any suggestions?
Thanks.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557
"Bernie Deitrick" wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:8C41042A-F785-4FD8-8C7D-(E-Mail Removed)...
> > 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
>
>
>
|
Similar Threads
