Possibly simple question regarding variable assignment and pivottables (VBA)

[Goose306]

Hi all,

The logic on this one is stumping me. Hoping someone can help me out, as I'm not quite following what the issue is here:

• I'm building a pivot table from data using VBA. When adding a row field, if I am adding it based on a variable it fails, but if I add it explicit it is fine. Relevant code snippet giving me fits:

'Objects
Dim pvt As PivotTable
Dim cat1 As PivotField
Dim wowws As Worksheet

'Set objects
Set wowws = ThisWorkbook.Worksheets("WoW")
Set pvt = pvc.CreatePivotTable(wowws.Cells(1,1), "Data")
Set cat1 = pvt.PivotFields("Date2")

With pvt
  With .cat1
    .Orientation = xlRowField
    .Position = 1
  End With
End With

• The error occurs on the "With .cat1" line, stating "Object doesn't support property or method". However, I can Print cat1.Name, and it sends me back the pivotfield name, and if I were to replace .cat1 on that line with .PivotFields("Date2"), it chugs along happily.

• This is making me pull my hair out, as there doesn't seem to be any rhyme or reason to this issue. I'm hoping I'm missing something super obvious here. (FYI: I do have to use a variable, cat1 is normally actually set via an IF statement for different options, this is how this one is set that is giving me fits.) I have also tried removing the Withs, as well (same result).

Excel 2013, 64-bit, for what it matters.

 

[Goose306]

Well, solved it (sort-of):

If I remove the "With .pvt" with statement, and leave it as just "With cat1" - it will work. I'm still unclear why this is - if I don't assign it as a pivotfield variable, it works fine - something specifically about the nested with statements with two variable assignments is causing issues. If anyone is able to shed light on that, it would be beneficial, as there is certainly times I would prefer to use both.