+ Reply to Thread
Results 1 to 2 of 2

Create Multiple Pivot Tables in the one Worksheet using vba

  1. #1
    Registered User
    Join Date
    05-11-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    1

    Create Multiple Pivot Tables in the one Worksheet using vba

    Hi All,
    I'm a real novice at vba so was hoping someone could help me out. I'm trying to create two pivot tables in the one worksheet using a macro, however when it runs I'm getting an error because each time I run the macro the name of the second pivot table changes. How can I use a variable to ensure that it doesn't matter what the name of the pivot table is?
    My plan is for users to run the macro and a new sheet will be created that will contain both Pivot Tables. The raw data that users will paste in will always be the same number of columns but will have different numbers of rows. The first part of my code adds columns that I will need for my pivot table and then it turns the raw data into a Table. I managed to get this to work and create the first Pivot Table no matter what the name of the Table, however I'm having real trouble with the Pivot Tables and it seems to breakdown because the names of my Pivot Tables are always changing.
    I've attached the sample file.
    Any help would be great.
    Cheers, Aaron.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Create Multiple Pivot Tables in the one Worksheet using vba

    Hi Buzza,

    Sorry if I'm not helping.
    I just want to give an idea that if it's me,
    I prefer to have another sheet with a blank table rather than create a new table based on the sheet "Raw Data".

    So, what I do is something like this :
    Assuming that in sheet "Raw Data", the data which is going to be a table is always starts from column B to F row 12,
    I make a dynamic named range in "Name Manager" of Excel, where in the reference range box is a formula like this :
    Please Login or Register  to view this content.
    Say I name that range is "InputData".


    Next, I prepare a sheet (say, sheet XXX), with a table with one blank one row :
    EXCEL_2020-05-11_17-39-03.png
    Say, I name the table "DataTable". This is still a blank table.


    Next, I prepare the pivot table based on "DataTable" table, say on sheet YYY.
    Design the pivot table to whatever I like,
    have which header name is the ReportFilter, which one is the Row Labels, etc. For example like this :
    EXCEL_2020-05-11_18-02-39.png
    This is still a blank pivot table. Say I name it as "ptMain".


    Then have a vba where the process is :

    1. clear the "DataTable" table (which is in Sheet XXX) from cell A2 to the last data

    2. copy "InputData" range (where the range is in Sheet Row Data)

    3. paste it to cell A2 of "DataTable" in Sheet XXX ...
    the "DataTable" table now look like this :
    EXCEL_2020-05-11_17-54-12.png

    4. fill the rest of the header of "DataTable" with the formula as needed.
    the "DataTable" table now look like this :
    EXCEL_2020-05-11_17-57-14.png


    5. refresh the "ptMain" pivot table (which is in Sheet YYY).

    6. then do the code to whatever I want to see in "ptMain" pivot table.



    Here is the code example to do number 1 to 5 :
    Please Login or Register  to view this content.
    Last edited by karmapala; 05-11-2020 at 06:41 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create multiple pivot tables/files with loops
    By ATXnative83 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2014, 07:46 PM
  2. Replies: 0
    Last Post: 03-22-2014, 10:56 AM
  3. Replies: 0
    Last Post: 07-25-2013, 05:24 PM
  4. Create Multiple Pivot Tables VBA
    By bcn1988 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2013, 12:26 PM
  5. Create multiple pivot tables from pivot cache
    By dpatel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-09-2010, 12:24 PM
  6. [SOLVED] Need to create multiple Pivot tables at once
    By havocdragon in forum Excel General
    Replies: 3
    Last Post: 07-27-2006, 06:16 PM
  7. [SOLVED] How do i create multiple pivot tables with one field automatically
    By Leonardo in forum Excel General
    Replies: 1
    Last Post: 06-16-2006, 04:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1