VBA: Die PivotTables-Eigenschaft des ....
01.06.2016 16:40:02
Honigdachs
ich komme bei folgendem Code nicht weiter wegen der Fehlermeldung Die PivotTables-Eigenschaft des Worksheet-Objektes kann nicht zugeordnet werden.:
Sub pivot_tables()
Dim PT As PivotTable, PTField As PivotField
Dim location As String
Dim first_year As Integer
Dim last_year As Integer
Set PT = Sheets("scorecard data - 1").PivotTables("PivotTable1")
location = Worksheets("event detection").Range("B1")
first_year = Worksheets("event detection").Range("E2").Value
last_year = Worksheets("event detection").Range("E6").Value
With PT
.ManualUpdate = True
For Each PTField In .DataFields
PTField.Orientation = xlHidden
Next PTField
.ManualUpdate = False
End With
For i = first_year To last_year
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1"). _
PivotFields(location & CStr(i)), "number of companies in " & i, xlSum
Next
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable1").PivotFields("country").AutoSort _
xlDescending, "number of companies in 2015"
End Sub
Danke!