Pivot 1004 run error why?

[Jenst]

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

[mana]

Same Question?

Pivot table Error
http://www.vbaexpress.com/forum/showthread.php?57268-Pivot-table-Error

[Jenst]

Hi mana! Thanks for your post. In this thread I attached a different document, because my table was not correct first time. I got to the bottom of the problem. The table header included characters which caused error.

Thanks mana for giving me a helping hand every time!!