Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 29 of 29

Thread: How to use VBA Slicer to connect two PivotTable

  1. #21
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  2. #22
    VBAX Regular
    Joined
    Jul 2018
    Posts
    23
    Location
    Quote Originally Posted by p45cal View Post
    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
    Awesome! Thanks a lot

  3. #23
    VBAX Regular
    Joined
    Jul 2018
    Posts
    23
    Location
    Quote Originally Posted by p45cal View Post
    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.

    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
    Thanks for your time

  4. #24
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  5. #25
    VBAX Regular
    Joined
    Jul 2018
    Posts
    23
    Location
    Quote Originally Posted by p45cal View Post
    Put them there from the outset:
    Set SLCR = sc.Slicers.Add(ActiveSheet, , pf.Name, pf.Name, TheTop, 648, 144, 198.75)
    change the red.
    Thank You p45cal

  6. #26
    VBAX Regular
    Joined
    Jul 2018
    Posts
    23
    Location
    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.

  7. #27
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  8. #28
    VBAX Regular
    Joined
    Jul 2018
    Posts
    23
    Location
    Quote Originally Posted by p45cal View Post
    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

    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

  9. #29
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •