Consulting

Results 1 to 11 of 11

Thread: Change Value on Multiple Pivot Tables at Once

  1. #1
    VBAX Regular
    Joined
    Sep 2019
    Posts
    6
    Location

    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!

  2. #2
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    271
    Location
    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

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    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

    Capture.JPG


    Intermediate list to use in the PTs


    Capture2.JPG


    Look at the attachment as see if it can help

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

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Sep 2019
    Posts
    6
    Location

    Sample Data set

    Sample Data.xlsx

    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.

  5. #5
    VBAX Regular
    Joined
    Sep 2019
    Posts
    6
    Location
    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.

  6. #6
    VBAX Regular
    Joined
    Sep 2019
    Posts
    6
    Location
    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.

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    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

    Capture.JPG

    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
    Attached Files Attached Files
    Last edited by Paul_Hossler; 09-11-2019 at 09:24 AM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Regular
    Joined
    Sep 2019
    Posts
    6
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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

    Capture.JPG

    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.

  9. #9
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Sep 2019
    Posts
    6
    Location
    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..

  11. #11
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    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

    Capture.JPG
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •