Try changing
For Each pf In pt.PivotFields
to
For Each pf In pt.RowFields
or
For Each pf In pt.PageFields
or
For Each pf In pt.ColumnFields
Printable View
Try changing
For Each pf In pt.PivotFields
to
For Each pf In pt.RowFields
or
For Each pf In pt.PageFields
or
For Each pf In pt.ColumnFields
I got a new issue. now i want to select all the slicers and move to a hidden sheet. I got codes in place to create a new sheet and hide it.
I just want code now to select all the slicers in the activesheet. I also have couple of charts in the same sheet, i dont want to select the charts. i used select all shapes and even charts got selected. so i am looking for something like below.
Thanks for your time :yesCode:Sheets.add After:=ActiveSheet
ActiveSheet.Name = "Hidden"
Sheets("Master").Select
' I need code here to select all the slicers in the master sheet.
Selection.ShapeRange.Group.Select
Selection.Cut
Sheets("Hidden").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.Visible = False
Put them there from the outset:
Set SLCR = sc.Slicers.Add(ActiveSheet, , pf.Name, pf.Name, TheTop, 648, 144, 198.75)
change the red.
I could'nt find the respective thread, so posting here since its relevant to this.
I need help on modifying show report filter page code.
I have two pivot tables in the master sheet say pivot table 10A & 10B with different row / column & value field but pagefield is same.
1) Now i need to create a new worksheet for every pivot item of pivot field "Building" of pivot table 10A which i can get using show report filter page. Say the worksheets are now named Building 1 , Building 2, Building 3, Building 4.
2) Now, From pivot table 10 B, i need to create pivot table for every pivot item of pivot field "Building". if i use show report filter page the new sheets are created and named Building 1(1) , Building 2(1), Building 3(1), Building 4(1)
What i am looking at is when i am generating sheets from pivot table 10B, i need new pivot tables to be placed in its respective existing sheet (Building 1 , Building 2, Building 3, Building 4) and in range D1.
This one is difficult to provide a robust solution to.
The Show report filter pages command makes multiple new sheets when the command is executed. There is no control over where the pivot tables go, new sheets are always created.
Two ways you might go:
1. Exceute the Show report filter pages for each pivot table, then go hunting for the newly created sheets and try to match up the similar names then move pivot tables around, then delete redundant sheets.
2. Instead of using the Show report filter pages command, try to simulate what it does by copying the pivot table according to what's selected in the page field(s), and set up the copied pivots in the same way as the Show report filter pages command would have. You do have control over where the copied pivots go.
Either way, a considerable amount of coding is required.
I suggest posting a brand new thread for this question.
Thanks p45cal. But I some how figured out for this, Have posted the code below, will be helpful for others in future
Code:Sub Filter_Pages()
Dim i As Integer
Dim sItem As String
Dim pivotSht As Worksheet
Dim ws As Worksheet
Dim PT1 As PivotTable
Dim PT2 As PivotTable
Set pivotSht = Sheets("Master")
Application.ScreenUpdating = False
Set PT1 = pivotSht.PivotTables("10A")
Set PT2 = pivotSht.PivotTables("10B")
PT1.ShowPages PageField:="Building"
pivotSht.move Before:=Worksheets(1)
With PT2
With .PivotFields("Building")
.PivotItems(1).Visible = True '---hide all items except item 1
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
If i <> 1 Then .PivotItems(i - 1).Visible = False
sItem = .PivotItems(i)
With PT2.PivotFields("Building")
.PivotItems(i).Visible = True
EnableMultiplePageItems = False
With PT2.TableRange2.Copy
Sheets(sItem).Activate
With ActiveSheet
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With
End With
End With
Next i
End With
End With
End Sub
Try
Code:
For Each pf In pt.PageFields