Add fields to PivotTable Report with VBA

[BryanMohr]

I am successfully creating a pivottable based on usedrange for a source table that has varying columns and rows. How can I use VBA to select all available fields to add to the report? Right now it comes up with the "Available Field List" so I manually check the all of the available fields, then I can continue on with the VBA code that does other stuff (like adds conditional formatting). I need to be able to automatically select all of the fields listed to add to the report.

Also, is there a way to get a "usedrange" for a pivottable? I need to add conditional formatting to all cells that have data in them so if I could get the used range, I could do that pretty easily with a nested for loop.

This is kind've urgent so if anyone can just give me some pointers, I would really appreciate it.

 

[Worf]

Hello and welcome to the Board!

See if this example is useful for you, needing further help please post back:

Sub CreatePivotTable()
Dim PTCache As PivotCache, PT As PivotTable, st$, i%


Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Range("A1").CurrentRegion)


Set PT = ActiveSheet.PivotTables.Add(PivotCache:=PTCache, _
    TableDestination:=Range("b18"))


st = ""
For i = 1 To PT.PivotFields.Count
    st = st & PT.PivotFields(i).Name & vbNewLine
Next
MsgBox st, vbInformation, "Available Fields"


With PT         ' add fields
    .PivotFields(2).Orientation = xlPageField
    .PivotFields(3).Orientation = xlColumnField
    .PivotFields(1).Orientation = xlRowField
    .PivotFields(4).Orientation = xlDataField
    .DisplayFieldCaptions = False
End With


MsgBox "Data is at " & PT.DataBodyRange.Address & vbNewLine & _
"Table is at " & PT.TableRange1.Address & vbNewLine _
& "Whole object is at " & PT.TableRange2.Address, 64, PT.Name


End Sub