JaneP
01-05-2019, 04:24 AM
Using Excel 206 (office 365)
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. After many days 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.
Ive tried to write a small sub to loop through the combinations list I used to create the Pivot Tables - this 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 it to use For loop and variables for pivot table and slicer name. I get an error when doing the .addPivotTable
Code
Sub SetSLicersPivotTables()
'Declare the variables
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 sheet, and source table name (fixed for now)
'
Set wksSource = ThisWorkbook.Worksheets("KpiTerm")
'
' Set values to variables - maybe later change to search header row for column name to get value
'
PtNamePos = 2
SlicerNamePos = 4
'
' make sure on destination worksheet - assumption is start cell selected
'
wksSource.Activate
' Copy combinations into array
' check how many pivot tables (from no of rows) and set counter NoOfPivots
'
sCustomList = Worksheets("combinationsneededTerm").ListObjects("combinations").DataBodyRange.Value
PivCount = Worksheets("combinationsneededTerm").ListObjects("combinations").ListRows.Count
'
' for each row - create Pivot Table for each
'
For i = 1 To PivCount
If sCustomList(i, SlicerNamePos) <> "" Then
'
'connect pivot to slicer
'
SlicerName = "Slicer_" & sCustomList(i, SlicerNamePos)
ActiveWorkbook.SlicerCaches(SlicerName).PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables(sCustomList(i, PtNamePos)))
End If
'
'next pivot
'
Next i
' all done hopefully!
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub
23508235092351023512
Sample from combinations list
Sourcename
Pivottable name
PivotTable type
Slicer
PT-Field1
PT-Field2
PT-Field3
PT-Field4
PT-Field5
PT-Field6
PT-Field7
PT-Field8
DataCalctype1
Datafield1
DataFldCaption1
DataCalctype2
Datafield2
DataFldCaption2
DataFieldPos
OutcomesTermTable
TermNoOfResponses170
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoOfResponses172
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoOfResponses174
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoOfResponses176
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses170
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses172
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses174
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses176
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermTargetDistance170
Table
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_DummyHeadline
T_Focussed
Average
T_final response %calc
Target
T_Average
T_improve %calc
Distance
Row
Would really appreciate any help to get this sub working
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. After many days 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.
Ive tried to write a small sub to loop through the combinations list I used to create the Pivot Tables - this 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 it to use For loop and variables for pivot table and slicer name. I get an error when doing the .addPivotTable
Code
Sub SetSLicersPivotTables()
'Declare the variables
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 sheet, and source table name (fixed for now)
'
Set wksSource = ThisWorkbook.Worksheets("KpiTerm")
'
' Set values to variables - maybe later change to search header row for column name to get value
'
PtNamePos = 2
SlicerNamePos = 4
'
' make sure on destination worksheet - assumption is start cell selected
'
wksSource.Activate
' Copy combinations into array
' check how many pivot tables (from no of rows) and set counter NoOfPivots
'
sCustomList = Worksheets("combinationsneededTerm").ListObjects("combinations").DataBodyRange.Value
PivCount = Worksheets("combinationsneededTerm").ListObjects("combinations").ListRows.Count
'
' for each row - create Pivot Table for each
'
For i = 1 To PivCount
If sCustomList(i, SlicerNamePos) <> "" Then
'
'connect pivot to slicer
'
SlicerName = "Slicer_" & sCustomList(i, SlicerNamePos)
ActiveWorkbook.SlicerCaches(SlicerName).PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables(sCustomList(i, PtNamePos)))
End If
'
'next pivot
'
Next i
' all done hopefully!
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub
23508235092351023512
Sample from combinations list
Sourcename
Pivottable name
PivotTable type
Slicer
PT-Field1
PT-Field2
PT-Field3
PT-Field4
PT-Field5
PT-Field6
PT-Field7
PT-Field8
DataCalctype1
Datafield1
DataFldCaption1
DataCalctype2
Datafield2
DataFldCaption2
DataFieldPos
OutcomesTermTable
TermNoOfResponses170
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoOfResponses172
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoOfResponses174
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoOfResponses176
DataModel
T_1st or Only
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses170
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses172
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses174
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_DummyHeadline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermNoPairedResponses176
DataModel
T_1st or last
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_DummyACt
T_Headline
T_Focussed
Distinct Count
T_Reference
Distinct Count of T_Reference
Data
OutcomesTermTable
TermTargetDistance170
Table
T_DummyBorough
T_DummyProgType
T_Programme2
T_DummyOrg
T_ParentType
T_Activity Type
T_DummyHeadline
T_Focussed
Average
T_final response %calc
Target
T_Average
T_improve %calc
Distance
Row
Would really appreciate any help to get this sub working