Pivot 1004 run error
27.09.2016 01:46:21
Jens
nach einer Rückmeldung von Luschi habe ich bemerkt, dass die beigefügte Tabelle nicht korrekt war. ich brauche weiterhin dringend Hilfe:
Ich verzweifle an meinem Pivot Code:(. Folgender Teil des Codes produziert Runtime error 1004:
With pvt8
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
Ich habe für die Pivottables dadrüber genau den gleichen Code verwendet und sie werden einwandfrei geschrieben. Ich habe alles probiert und komme nicht dahinter, was der Grund für den Error ist. Das xls habe ich beigefügt. Danke für eure Hilfe!
Datei:
https://www.herber.de/bbs/user/108426.xlsm
Code:
Option Explicit
Sub pivot()
'''''''''''K Variables
Dim loopcountermk As Long
Dim Modulendk As Long
Dim Modulstak As Long
Dim Modulnumk As Long
Modulstak = 27
Modulnumk = 8
Modulendk = Modulstak + Modulnumk
Modulendk = Modulendk - 1
'''''''''''I Variables
Dim loopcountermi As Long
Dim Modulendi As Long
Dim Modulstai As Long
Dim Modulnumi As Long
Modulstai = 36
Modulnumi = 8
Modulendi = Modulstai + Modulnumi
Modulendi = Modulendi - 1
'''''''''t3 variables
Dim Startto As Long
Dim loopcounterto As Long
Dim Modulendto As Long
Dim Modulstato As Long
Dim Modulnumto As Long
Modulstato = 27
Modulnumto = 8
Modulendto = Modulstato + Modulnumto
Modulendto = Modulendto - 1
''''t3 variables count
Dim loopcountertoc As Long
Dim modulstatoc As Long
Dim modulnumtoc As Long
Dim modulendtoc As Long
ReDim modultoc(1 To Modulnumk)
modulstatoc = 44
modulnumtoc = 3
modulendtoc = modulstatoc + modulnumtoc
modulendtoc = modulendtoc - 1
''''''''b3 variables count
Dim loopcounterboc As Long
Dim modulstaboc As Long
Dim modulnumboc As Long
Dim modulendboc As Long
ReDim modulboc(1 To Modulnumk)
modulstaboc = 48
modulnumboc = 3
modulendboc = modulstaboc + modulnumboc
modulendboc = modulendboc - 1
'''''''''''o variables
Dim loopcountero As Long
Dim Modulendo As Long
Dim Modulstao As Long
Dim Modulnumo As Long
Modulstao = 53
Modulnumo = 4
Modulendo = Modulstao + Modulnumo
Modulendo = Modulendo - 1
'' K Pivot
Dim pvc As PivotCache
Dim pvt As PivotTable
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim pvt1 As PivotTable
Dim pvt2 As PivotTable
Dim pvt3 As PivotTable
Dim pvt4 As PivotTable
Dim pvt5 As PivotTable
Dim pvt6 As PivotTable
Dim pvt7 As PivotTable
Dim pvt8 As PivotTable
Set ws1 = Worksheets("Numbers")
Set ws2 = Worksheets("Sheet2")
Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A3").CurrentRegion)
Set pvt = pvc.CreatePivotTable(TableDestination:=ws2.Range("A3"))
For loopcounterto = Modulstato To Modulendto
With pvt
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
Next loopcounterto
With pvt.DataPivotField
.Orientation = xlRowField
.Position = 1
End With
'' I pivot
Sheets("Numbers").Activate
Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A3").CurrentRegion)
Set pvt1 = pvc.CreatePivotTable(TableDestination:=ws2.Range("A16"))
For loopcounterto = Modulstai To Modulendi
With pvt1
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
Next loopcounterto
With pvt1.DataPivotField
.Orientation = xlRowField
.Position = 1
End With
'' O pivot
Sheets("Numbers").Activate
Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A3").CurrentRegion)
Set pvt8 = pvc.CreatePivotTable(TableDestination:=ws2.Range("A61"))
For loopcounterto = Modulstao To Modulendo
With pvt8
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
Next loopcounterto
With pvt8.DataPivotField
.Orientation = xlRowField
.Position = 1
End With
'' 1First 3
Sheets("Numbers").Activate
Set pvt2 = pvc.CreatePivotTable(TableDestination:=ws2.Range("A30"))
With pvt2
.PivotFields(ws1.Cells(2, modulstatoc).Value).Orientation = xlRowField
End With
With pvt2
.PivotFields(ws1.Cells(2, modulstatoc).Value).Orientation = xlDataField
End With
'' 2First 3
Sheets("Numbers").Activate
Set pvt3 = pvc.CreatePivotTable(TableDestination:=ws2.Range("C30"))
With pvt3
.PivotFields(ws1.Cells(2, modulstatoc + 1).Value).Orientation = xlRowField
End With
With pvt3
.PivotFields(ws1.Cells(2, modulstatoc + 1).Value).Orientation = xlDataField
End With
'' 3First 3
Sheets("Numbers").Activate
Set pvt4 = pvc.CreatePivotTable(TableDestination:=ws2.Range("E30"))
With pvt4
.PivotFields(ws1.Cells(2, modulendtoc).Value).Orientation = xlRowField
End With
With pvt4
.PivotFields(ws1.Cells(2, modulendtoc).Value).Orientation = xlDataField
End With
'' Bot 3
Sheets("Numbers").Activate
Set pvt5 = pvc.CreatePivotTable(TableDestination:=ws2.Range("A46"))
With pvt5
.PivotFields(ws1.Cells(2, modulstaboc).Value).Orientation = xlRowField
End With
With pvt5
.PivotFields(ws1.Cells(2, modulstaboc).Value).Orientation = xlDataField
End With
'' 2Bottom 3
Sheets("Numbers").Activate
Set pvt6 = pvc.CreatePivotTable(TableDestination:=ws2.Range("C46"))
With pvt6
.PivotFields(ws1.Cells(2, modulstaboc + 1).Value).Orientation = xlRowField
End With
With pvt6
.PivotFields(ws1.Cells(2, modulstaboc + 1).Value).Orientation = xlDataField
End With
'' 3bottom 3
Sheets("Numbers").Activate
Set pvt7 = pvc.CreatePivotTable(TableDestination:=ws2.Range("E46"))
With pvt7
.PivotFields(ws1.Cells(2, modulendboc).Value).Orientation = xlRowField
End With
With pvt7
.PivotFields(ws1.Cells(2, modulendboc).Value).Orientation = xlDataField
End With
pvt.DataPivotField.Caption = "Knowledge"
pvt1.DataPivotField.Caption = "Impact"
pvt2.CompactLayoutRowHeader = "Top3 Ranking1"
pvt3.CompactLayoutRowHeader = "Top3 Ranking2"
pvt4.CompactLayoutRowHeader = "Top3 Ranking3"
End Sub