+ Reply to Thread
Results 1 to 1 of 1

Need help with tweaking VBA and/or PIVOT tables

  1. #1
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    208

    Need help with tweaking VBA and/or PIVOT tables

    Experts:

    I need some help with tweaking existing VBA code AND/OR tweaking pivot tables/slicers. In a previous thread (several months ago), expert "jindon" provided some superb help and helped me create the existing VBA.

    At this time, however, I'm trying to integrate a new data section and need to slightly modify the existing "transpose" process.

    The entire explanation for the process could be lengthy. I will do my very best to keep it simple. Allow me to provide some background first:

    BACKGROUND:
    - The existing XLSM includes seven (7) tabs:
    1. "MasterData"
    2. "Trs_Units"
    3. "Trs_Section1"
    4. "Trs_Demographics"
    5. "PivotUnits"
    6. "PivotSection1"
    7. "PivotDemographics"

    - On the tab "MasterData", there a three (3) sections:
    1. "UNIT INFORMATION" -- a merged cell in A1
    2. "INFO_SECTION_ONE" -- a merged cell in H1
    3. "INFO_DEMOGRAPHICS" -- a merged cell in Q1

    - Values in A2:S2 are the individual "column headers"
    - Starting in row 3, I have values for 4 *sample* records, where ...
    -- A3:G6 are string/date values;
    -- H3:P6 are "numeric" values;
    -- Q3:S6 are string values

    EXISTING PROCESS:
    - User enters new data (e.g., a 5th record) and completes all values across column A:S
    - Upon leaving (i.e., deactivating) the tab "MasterData" (there are other tabs not included in this sample XLS), all existing/new data from the "MasterData" tab are automatically transposed into associated tables (all tabs with prefix "Trs" short for "transpose").
    - For example, the table on tab "Trs_Units" will be updated. The **key** is that column H shows a "1". This will allow me to use a pivot table and generate some graphs.
    - The creation of the "1" in column H is based on the VBA code (on sheet "MasterData") starting with line 26 & 27: "Next" / "With Sheets("Trs_Units")". Specifically, line 34 places the "1" into column H.

    EXISTING PROCESS (cont'd for "Trs_Section1"):
    - Once you go to tab "Trs_Section1", the process is slightly different though.
    - That is, here column H shows the column header value while column I shows the associated numeric value. Again, I use pivot tables to graph some information.

    All of this works well... again, expert "jindon" provided some significant help a few months ago.

    Here's where I need some help... again some background first:
    - The 4th and 7th tab "Trs_Demographics" & "Pivot_Demographics" are new tabs.
    - For the prior/existing tabs "PivotUnits" and "PivotSection1", I can use slicers to focus on the correct data. This is due to "Units" having the converted "1" value while "Section1" data is all numeric.
    - In contrast though, "PivotDemographics" does not work the same way. Here, I have all of my column labels (Gender, Other 1, Other 2) as my row labels.
    - Ultimately though, I may only want to show "Gender AND NOT "Other 1" or "Other 2" in the same pivot/slicer. I probably would want separate pivot tables and associated slicers for both "Other 1" and "Other 2".

    My question:
    Given that the VBA adds a "1" to the unit information, is there a way to re-code the "INFO_DEMOGRAPHICS" section so that I also get "1" values in the associated "Trs_Demographics" tab?
    If not, can the pivot table be modified so that I get one pivot table that shows me "Gender" alone; also, I then would want two separate pivot tables for the other columns, e.g., "Other 1" and "Other 2". How can this be accomplished?

    I hope this is NOT too confusing. I believe, however, if you look at the data in the XLS, it will/should make some sense.

    Thank you for any help in advance.

    Cheers,
    Tom
    Attached Files Attached Files
    Last edited by skydivetom; 11-20-2020 at 10:10 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. VBA Question - Tweaking a pivot table with VBA
    By pritchard.ev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2014, 02:52 PM
  2. Creating a Master / Consolidated Pivot Tables from a multiple pivot tables
    By paradise2sr in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 12-12-2013, 02:28 AM
  3. Creating a Master / Consolidated Pivot Tables from a multiple pivot tables
    By paradise2sr in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-08-2013, 10:49 PM
  4. Pivot Tables - need to do separate pivot tables for multiple sheets in same format
    By tconnell1965 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2013, 07:04 AM
  5. Validating Data Between 2 Pivot Tables (Pivot Tables Don't Line Up)
    By JohnGC84 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-18-2013, 05:07 PM
  6. Time sheet tweaking, weeknum, and pivot tables
    By gortw4j in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-12-2012, 01:44 AM
  7. Replies: 0
    Last Post: 09-22-2012, 07:22 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