locked
In VBA - trying to link pivot tables to existing slicer(s) but get error DesperateFor help Please RRS feed

  • Question

  • Got deadline of end tomorrow

    At the charity i volunteer for I'm trying to analyse our clients attendance data to use for funding applications, funder feed back, managers and trustees.  As the stats needed varies according to who needs it I have an extremely large number of pivot tables. The data we collect recently changed so I had to update around 1000 pivot tables.  I've managed to automate reproducing the pivot tables from scratch, with source types a mix of  database and external but the code id used to connect Pivots and  slicer failed.

    'Extract from code
    '
    'three different attempts/ways to get working.  
    '
    'pick next pivottable name and slicer name from array list - ERROR: invalid object or parameter
    '
                    ActiveWorkbook.SlicerCaches(SlicerName).PivotTables.AddPivotTable ( _
                    ActiveSheet.PivotTables(sCustomList(i, PtNamePos)))
    '
    ' with pt as String variable- ERROR - ERROR: invalid object or parameter
    '
                    ActiveWorkbook.SlicerCaches(SlicerName).PivotTables.AddPivotTable ( _
                    ActiveSheet.PivotTables(ptName))
              
    '
    'recordedcode - does not giver error but dslicer does not show pivot table asselected
    '
               
              ActiveWorkbook.SlicerCaches("Slicer_T_1st_or_only").PivotTables.AddPivotTable ( _
            ActiveSheet.PivotTables("TermNoOfResponses170"))

    Sub SetSlicersPivotTables()
    
        Dim wksSource As Worksheet  'sheet where pivots are
       
        Dim CurrRow As Long
        Dim i As Long
           
        Dim SlicerNamePos As Integer
        Dim SlicerName As String
        Dim PtNamePos As Integer
    '
    ' need to define array of combinations and set to table in Combinationsneeded
    '
        Dim PivCount As Long
        Dim sCustomList As Variant
    '
    ' Assign the source 
    '
        Set wksSource = ThisWorkbook.Worksheets("Outcomes Term")
        wksSource.Activate  
    '
    ' Set values to variables - maybe later change to search header row for column name to get value
    '
            PtNamePos = 2
            SlicerNamePos = 4
    
    ' Copy combinations into array
    ' check how many pivot tables (from no of rows) and set counter
    '
         sCustomList = Worksheets("combinationsneededTerm").ListObjects("combinations").DataBodyRange.Value
         PivCount = Worksheets("combinationsneededTerm").ListObjects("combinations").ListRows.Count
    '
    ' for each row - check if Slicer name specified
    '
        For i = 1 To PivCount
               If sCustomList(i, SlicerNamePos) <> "" Then
    '
    'connect pivot to slicer
    '
    		SlicerName = "Slicer_" & sCustomList(i, SlicerNamePos)
    '
    'following gives error Invalid procedure call or argument
    '    
                    ActiveWorkbook.SlicerCaches(SlicerName).PivotTables.AddPivotTable ( _
                    ActiveSheet.PivotTables(sCustomList(i, PtNamePos)))
         
               End If
    '
    'next pivot
    '
        Next i
    
    End Sub



    Ive tried to write a small sub to loop through the list used to create the Pivot Tables that specifies the pivot table name and the slicer it need to connect to.  I used record  to get the code to start with  and modified to use For loop and variables for pivot table and slicer name. I get an error when doing the .addPivotTable
    • Edited by JaneOAP Sunday, January 6, 2019 9:48 AM
    Friday, January 4, 2019 10:06 PM