PDA

View Full Version : Pivot table Error



Jenst
09-26-2016, 03:28 AM
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 works :banghead: and 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
17174

mana
09-27-2016, 07:10 AM
I don't have the energy to read your long codes.

You should check the cells(2,53).value.
It seems to be empty?






Modulstao = 53

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