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
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
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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 timeSheets.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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Thanks p45cal. But I some how figured out for this, Have posted the code below, will be helpful for others in future
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
For Each pf In pt.PageFields
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3