Hi! I'm going mad over pivot tables again. I have the following code which produces different pivot tables. Based on the unstructured table in the document attached. In this line I get an error message 1004:
With pvt8
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
I just copied the line from the pivot aboe and adjusted it, there it works perfectly. Do anyone has an idea why this error is occuring. I tried everything from rewriting, restructuring the table, restructuring the code, nothing worksand I can't explain why. Thanks for your help!
Code:
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 modulentoc 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 modulenboc 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 pvt2 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 = pvc1.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 = pvc1.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 = pvc1.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 = pvc1.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 = pvc1.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 = pvc1.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
Sample_1.xls
Reply With Quote
Originally Posted by Jenst
