PivotTable RowFields Arrays Question

[jvanbonn]

Can someone tell me why the following to attempts to assign RowFields to a PivotTable (PT) are not equivalent? Or better yet, why can I not use an Array Variable to assign RowFields?

Dim PT As PivotTable
...
Dim HeaderArray() As String
...
PT.AddFields RowFields:=[B][COLOR=Red]HeaderArray[/COLOR][/B]
PT.AddFields RowFields:=[B][COLOR=Red]Array("Region", "Customer")[/COLOR][/B]
...

Thanks in advance!

 

[RoryA]

They both work for me - what does the missing code look like, and what error do you get?

 

[jvanbonn]

Here is the Macro so far:

I have commented out the HeaderArray portions and hard coded an array, but I'd like it to be dynamic.

Option Explicit
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
    SheetExists = False
    On Error GoTo NoSuchSheet
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
NoSuchSheet:
End Function
Sub NewPivotTable()
'
' NewPivotTable Macro
'
Dim PTSheet As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Variant
[COLOR=Red]'''Dim HeaderArray() As Variant, i As Integer, FinalColumn As Integer
'''Dim Header As Variant[/COLOR]

'Delete old "Pivot" worksheet
If SheetExists("Pivot") Then
    Application.DisplayAlerts = False
    Sheets("Pivot").Delete
    Application.DisplayAlerts = True
End If

Set PTSheet = Worksheets.Add
    PTSheet.Name = "Pivot"

Set PRange = Worksheets("temp").Range("A1:M147")
 '147 Rows & 13 Columns w/ headers in row 1

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="'temp'!" & PRange.Address(, , xlR1C1))
  
Set PT = PTCache.CreatePivotTable(TableDestination:="'Pivot'!R3C1", _
            TableName:="PivotTable1")

[COLOR=Red]'''FinalColumn = Worksheets("temp").Cells(1, Columns.Count).End(xlToLeft).Column
'''ReDim HeaderArray(FinalColumn)

'''For i = 1 To FinalColumn Step 1[/COLOR] [COLOR=Red]
'''    If Worksheets("temp").Cells(1, i).Value = "PM1" Then
'''      HeaderArray(i) = "PM"
'''    Else
'''      HeaderArray(i) = Worksheets("temp").Cells(1, i).Value
'''    End If
'''  'MsgBox i & "  " & HeaderArray(i)
'''Next i[/COLOR]

    PT.ManualUpdate = True
  [COLOR=Red]' ' 'PT.AddFields RowFields:=HeaderArray() ', PageFields:="EA"[/COLOR]
    PT.AddFields RowFields:=Array("Co", "Rte", "PM1", "Bridge Number", _
        "Structure Name", "Work Description", "Estimated Cost"), _
          PageFields:="EA"

    
    ActiveWorkbook.ShowPivotTableFieldList = False
    PT.ShowDrillIndicators = False
    PT.DisplayFieldCaptions = True

    With PT
        .ColumnGrand = False
        .RowGrand = False
        .InGridDropZones = True
        .AllowMultipleFilters = True
        .RowAxisLayout xlTabularRow
    End With
    With PT.PivotCache
        .RefreshOnFileOpen = True
        .MissingItemsLimit = xlMissingItemsNone
    End With
    
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
Columns("A:A").ColumnWidth = 6
Columns("B:B").ColumnWidth = 4
Columns("C:C").ColumnWidth = 6
Columns("D:D").ColumnWidth = 8
Columns("E:E").ColumnWidth = 25
Columns("F:F").ColumnWidth = 50
Columns("G:G").ColumnWidth = 10

    
End Sub

 

[RoryA]

Your array's lower bound will be 0, but you are looping from 1.

 

[jvanbonn]

As usual, the answer was hidden in plain sight!

Thank you!

 

[Myproblem]

I have similar problem with arrays, the code below is from book Pivot Table Data Crunching with Excel 2007:
Sub CreatePivot()
' Page 206 of Pivot Table Data Crunching
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets("PivotTable")

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range("R1:AZ1").EntireColumn.Clear


' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)

' Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")

' Turn off updating while building the table
PT.ManualUpdate = True

' Set up the row & column fields
PT.AddFields RowFields:=Array("Business Segment", "Product"), _
ColumnFields:="Region"

' Set up the data fields
With PT.PivotFields("Revenue")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With

' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

' Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = "PivotStyleMedium10"

WSD.Activate
Range("R1").Select
End Sub

I get message: "Run-time error 1004. AddFields method of PivotTable class failed."
what is wrong?

 

[RoryA]

Are you sure the field names are correct?

 

[Myproblem]

Are you sure the field names are correct?

you are wright, column name did not match those in vba code, now i corrected and it works
thx man