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
Bookmarks