Jenst
09-26-2016, 04:51 PM
I have changed the code a little bit, still I weren't able to find a solution: I'm going mad on a 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 works :banghead: and I can't explain why. Thanks for your help!
Document:
17183
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
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 works :banghead: and I can't explain why. Thanks for your help!
Document:
17183
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