Set up pivot table with VBA?

[JaneHancock]

Hello all,

I have a couple of steps that I seem to be taking what feels like a hundred times a week when it comes to setting up pivot tables. This has made me think "isn't there a better way", but after googling around for a bit it does not look like there is any way to change the default field settings globally.

I am now wondering if there could be a way to achieve what I want with the use of a VBA macro, but my VBA skills with this are still very very limited.

Here is what I currently do manually:
1. Drag in all the fields that I need (I guess that would remain a manual task as it's not always the same fields)
2. Loop for each row label:
2.1. Click on field settings
2.2. In Subtotals & Filters tab, select "None" (default is automatic)
2.3. In Layout & Print tab, select "Show item labels in tabular form" (default is "show items in outline form")
2.4. Still in Layout & Print tab, select "Repeat item labels" (default unselected)
2.5. Press ok
2.6. next row label and back to 2.1. until all are done.


I tried recording it for one field and this is what it came up with:

Sub test()
    ActiveSheet.PivotTables("PivotTable16").PivotFields("Field Name").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    With ActiveSheet.PivotTables("PivotTable16").PivotFields("Field Name")
        .LayoutForm = xlTabular
        .RepeatLabels = True
    End With
End Sub

That does make some sense to me (I can identify the steps that the code is taking), but what I am not clear on in particular is how to kind of address just "whichever" pivot is on the active sheet (as this will not always be "PivotTable16") and then establish a loop for "all field names that are in the table", rather than as in this case just the example "Field Name". I hope my explanation of the problem makes sense.

It would be fantastic if any of you who are experienced in VBA could tell me if what I am looking to do is possible and point me in the right direction. Of course if there should be a way to change the defaults without the need for VBA and I just haven't been able to find it, that would also be much appreciated!

Thanks a lot for any help or tips,
Jane

[sakmsb]

It would be fantastic if any of you who are experienced in VBA could tell me if what I am looking to do is possible and point me in the right direction. Of course if there should be a way to change the defaults without the need for VBA and I just haven't been able to find it, that would also be much appreciated!

Hope this would be helpful, go to excelvbasql.com and go thru the videos in Lesson 19: Pivot Tables . Pretty good videos on coding pivot tables.

[JaneHancock]

Thanks sakmsb, I found the link helpful. I haven't fully figured it out yet but it has given me a starting point