PDA

View Full Version : [SOLVED] Change Value on Multiple Pivot Tables at Once



empower
09-10-2019, 06:41 PM
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!

Artik
09-11-2019, 01:12 AM
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

Paul_Hossler
09-11-2019, 06:56 AM
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

25015


Intermediate list to use in the PTs


25016


Look at the attachment as see if it can help

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

empower
09-11-2019, 07:10 AM
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.

empower
09-11-2019, 07:11 AM
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.

empower
09-11-2019, 07:14 AM
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.

Paul_Hossler
09-11-2019, 08:46 AM
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

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

empower
09-11-2019, 09:52 AM
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

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.

Paul_Hossler
09-11-2019, 05:07 PM
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




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

empower
09-12-2019, 12:50 PM
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..

Paul_Hossler
09-12-2019, 03:08 PM
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



Option Explicit


Sub Macro1()
'
' Macro1 Macro
'


'
End Sub



25041