Add fields to PivotTable Report with VBA

BryanMohr

New Member
Joined
May 2, 2013
Messages
1
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,100
Hello and welcome to the Board!

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,329
Members
414,445
Latest member
walramgo02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top