CODEWITHSUNDEEP
vbapivot-table
J.Doe
J.Doe

Reputation: 339

Why is my code not producing a correct pivot table?

I have written a VBA program to create a pivot table and fill data. It works as expected, but it has lots of hard-coded parts, so I am rewriting it to make it more generic so I can reuse the subroutine. However, the rewritten code does not produce the same result as the original, and I don't know why.

I am passing the correct arguments, and it doesn't return any error. It actually creates an empty PivotTable, but it does not fill the data (RowFields).

Because there are many lines of code, I am only going to show the problematic part. (Please do let me know if you need more code to answer my question)

'This is the portion adding RowField, and this works, but it is hard coded. Too much of repetitive code!
With ActiveSheet.PivotTables("MyPivot").PivotFields("ColumnName1")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("MyPivot").PivotFields("ColumnName2")
    .Orientation = xlRowField
    .Position = 2
End With
With ActiveSheet.PivotTables("MyPivot").PivotFields("ColumnName3")
    .Orientation = xlRowField
    .Position = 3
End With

'And this is the rewritten code with loop, but it does not add any data to the pivot table.
'Argument1: ArrayRowField - a string array containing "Column1", "Column2", and "Column3"
'Argument2: PivotTableName - a string containing "MyPivot"
Dim counter As Integer
For counter = 0 To UBound(ArrayRowField)
    With ActiveSheet.PivotTable(PivotTableName).PivotField(ArrayRowField(counter))
        .Orientation = xlRowField
        .Position = (counter + 1)
    End With
Next counter

To me, these two look like they should behave the same way. But only the top one works, adding rowfields to the orientation, but the bottom one doesn't. Can anyone tell me what I am doing wrong?

Edit: I just tried taking with statement outside the loop, but it didn't make any difference.

'This did nothing to fix my problem
Dim counter As Integer
With ActiveSheet.PivotTable(PivotTableName).PivotField(ArrayRowField(counter))
    For counter = 0 To UBound(ArrayRowField)
        .Orientation = xlRowField
        .Position = (counter + 1)
    Next counter
End With

Upvotes: 2

Views: 37

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Has the "s" (two)

With ActiveSheet.PivotTables("MyPivot").PivotFields("ColumnName2")
                           ^                      ^

missing two "s"

With ActiveSheet.PivotTable(PivotTableName).PivotField(ArrayRowField(counter))

Upvotes: 3

Related Questions