Change Value on Multiple Pivot Tables at Once

Hi there,

I am a VBA beginner and seeking some help..

I have an excel workbook that contains the survey results to 10 questions (one column per question, one row per respondent). There are 17 pivot tables of analysis based on various demographic information that I have set up for the first question, but I would like to use VBA to quickly replicate these 17 pivot tables for the remaining 9 questions (instead of having to manually change out each question in the 'Values' section of the PivotTable Field 9x17 times).

Could someone help me with a VBA code that can update the 'Values' section of a PivotTable Field across multiple pivot tables at one time?

All of the articles I can find seem to be related to changing the value field settings on multiple pivots at once, but not the actual value itself.

Thanks for reading. I appreciate your time!

Attach a representative example in the workbook.
Show data and pivot table. Indicate what you want to change.
That's how you will help us answer and respect our time.

Artik

The usual issue is that the data is arranged to look good as data, but is not PT-friendly

For example, I'm guessing that your data looks like this in the corner

Happens to me all the time, so I wrote a (to me, and for me) a 'general purpose' macro to take the 2D data and reformat it into a more PT-friendly 1D list

Attachment 25015


Intermediate list to use in the PTs


Attachment 25016


Look at the attachment as see if it can help

If not then come back with a sample attachment and more information

Attachment 25018

Here is a representative sample of the data set I am referring to. I am looking for a VBA code (or a quick and easy way) to be able to update the pivot tables on the 'pivot tab' from Question 1 to Question 2.

Let me know what other information I can provide to be helpful.

Thanks again.

Hi Artik,

Thanks for your reply. I went ahead and replied to my original post with a sample data set. Let me know what other information I can provide.

Thanks again.

Hi Paul,

You are exactly correct. My data is organized similar to your first example (I went ahead and replied to my original post with a representative sample of my data).

Believe me, if I had my way, this data would absolutely be structured like your second example. Unfortunately, these survey responses are an export from Survey Monkey, so I do not have any control over the output.

In my actual data set, I have over 90,000 responses and 100 columns, so I am trying to avoid the manual process of reorganizing the data, if possible.

Yes, that's why I wrote the macro to avoid having to manually reorganize the data

Here's the macro in your sample.xlsx

I made the List worksheet that is more PT friendly and did 2 of your PTs as a test

A-D are your original, and F-I are using the PT List sheet

Attachment 25019

The only thing I had to do was put in NR (for 'No Response') into the demographic columns since the macro doesn't like blanks there (can be changed)

Each PT's page field (the question) is manually selected one-by-one, but that can be automated

Quote:

---

Originally Posted by Paul_Hossler

Yes, that's why I wrote the macro to avoid having to manually reorganize the data

Here's the macro in your sample.xlsx

I made the List worksheet that is more PT friendly and did 2 of your PTs as a test

A-D are your original, and F-I are using the PT List sheet

Attachment 25019

The only thing I had to do was put in NR (for 'No Response') into the demographic columns since the macro doesn't like blanks there (can be changed)

Each PT's page field (the question) is manually selected one-by-one, but that can be automated

---

Ah, that makes perfect sense. I must have misinterpreted your previous reply. If I can get this to work, this will make everything easier. I will give this a try!

Thank you so much for your time.

Playing around a bit more

1. Extracted the 'Make a List' logic to it's own module

2. Added a event handler to sync the question between PTs on that sheet

3. Some cleanup and simplifications

Code:

---

Option Explicit




Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim sQuestion As String
    Dim PT As PivotTable
   
    sQuestion = Target.PivotFields("QUESTION").CurrentPage


    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For Each PT In Me.PivotTables
        PT.PivotFields("QUESTION").ClearAllFilters
        PT.PivotFields("QUESTION").CurrentPage = sQuestion
    Next
    Application.EnableEvents = True
   
    Me.Columns("A:D").ColumnWidth = 100
    Me.Columns("A:D").AutoFit
   
    Application.ScreenUpdating = True
   
End Sub

---

Hi Paul,

Another dumb question..

How do I get the macro to run? I opened your latest excel workbook and went to View > Macros > View Macros > Macro1 > Run, and nothing happened.

Thanks for your patience with me. I know I am such a newbie..

I should have cleaned up a little - Macro1 is just a throw away from the macro recorder I used to quickly generate some macro lines that I integrated into the 'real' macro

Code:

---

Option Explicit


Sub Macro1()
'
' Macro1 Macro
'


'
End Sub

---

Attachment 25041