Need help with modifying VBA to reference columns (vs. cell values)

[skydivetom]

Experts:

In another post, expert oeldere provided significant assistance with generating some VBA which allows me to transpose data from one tab into another tab (different structure though).

At this time, I'd like to see if someone could assist me in modifying the code to make it dynamic based on column counts (vs. cell values). As they say, "a picture is worth a 1000 words". Therefore, please find attached XLSM where I provided much more details on tab "MasterData".

Again, expert oeldere really helped me setting up a great process in support of pivot tables and pivot graphs. In order for me to fully utilize the process, tweaking the VBA is required. I'd welcome anyone taking a stab at this challenge.

Thank you,
Tom

[alansidman]

If you are willing to use Power Query (as a 2010 user, you will need to download the add in. It is standard part of Excel in 2016 and later)

To unpivot the data is shown in the following Mcode and displayed on the same sheet you have the data unpivoted on

Please Login or Register  to view this content.

We can then add a combo box on the original data page and parametize it to show only the Factor you want listed.

For the parameter which is the combo box, we build Mcode as follows

Please Login or Register  to view this content.

Finally, we input the parameter into the first code.

Please Login or Register  to view this content.

Now when you use the drop down to select your Factor, (by the way which you can list all your factors), it will filter your data when you select the Refresh button on the Power Query Tab.

Here is your file that you can test once you add PQ to your Excel. No VBA is required to do this. All this code is generated by the User Interface.

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[jindon]

Try change to

Please Login or Register  to view this content.

[skydivetom]

jindon -- your solution is absolutely **BRILLIANT**!! I'm super-impressed how flexible the code.

Allow me to ask a small follow up question based on the code below:

Please Login or Register  to view this content.

Here, I've copied the same function now 3 times and replaced the "Alpha", "Bravo", and "Charlie" references where necessary. As you know, when each function is executed, I'm now creating 3 separate tabs based on the master data.

Now, here's my question:
As I only want one command button "Update Graphs" in cell A1, how can I create a separate function which then would call each of the three convert functions? Basically, let's say I change the command button's property from "ConvertData_Alpha" to "UpdateGraphs" and I then click it, what's the code in that function that calls/executes "ConvertData_Alpha" AND "ConvertData_Bravo" AND "ConvertData_Charlie" at the same time?

Again, thousand thanks for your help! Once I get to execute all 3 functions with 1 command button, I'd be set!

Cheers,
Tom

[jindon]

1) To the MasterData sheet code module

Please Login or Register  to view this content.

2) To a standard code module

Please Login or Register  to view this content.

[skydivetom]

Hmh... your code makes sense. Here's just one challenge...

In my example, I just "Alpha", "Bravo", etc. to simplify the scenario/problem. In my actual data, however, my categories name are longer. For instance, "Bravo" is actually "PREVENTATIVE ACTIONS (TYCOM or CMD Specific)". While this works fine with identifying the columns, the length of this string won't allow me to use them as worksheet names. For instance, the line of code "With Sheets("ConvertedData_" & myCategory)" would result in the tab name being chopped off.

So, is there a way to NOT make these dynamic and simply call the function? Generally, I like the "dynamic" approach but in this case, it would throw an issue.

[jindon]

Sheet name can not exceed 31 characters in total.
So you need to shorter prefix or category name.

[skydivetom]

Correct... my work-around was as follows:

While the value on MasterData was, e.g., ""PREVENTATIVE ACTIONS (TYCOM or CMD Specific)", I used the following line in my code:
With Sheets("Convert_PA_TYCOM")

Basically, the user likes the long name in the MasterData. The tab(s), however, will be hidden so I have flexibility using anything I like. The last XLSM example uses the "myCategory" though. So is there any way to rewrite it as such:

Please Login or Register  to view this content.

In this case, I can keep the long names but will use static worksheet names in the individual functions. The VBA for ConvertData is simply pseudo... what's the correct syntax for following this method though?

[jindon]

1) Replace bold part with the actual category name.

Please Login or Register  to view this content.

2) change to

Please Login or Register  to view this content.

[skydivetom]

Ok... I'll give that a shot. There's only one small change (I hope at least).

Line

Please Login or Register  to view this content.

... includes "Factor" in the 2nd line. In the hard-coded version, I replaced "Factor" with "PREVENTATIVE ACTIONS (TYCOM or CMD Specific)". Thus, once I add a slicer to the pivot table, the slicer uses that long title. Is there a way to replace "Factor" with a dynamic variable (the equivalent to shortened version "Alpha", "Bravo", etc.)?

[jindon]

try change to

Please Login or Register  to view this content.

[skydivetom]

Ok... I've made the change but I'm getting an error now. I have the following:

VBA on sheet "MasterData:

Please Login or Register  to view this content.

VBA in Module1:

Please Login or Register  to view this content.

Once I click the command button, I get an error (see attached). What am I missing?

[jindon]

You can not change 1st sub procedure like that.

Please Login or Register  to view this content.

What are you really trying to do?

[skydivetom]

Ah, I forgot that part... it works now.

Getting close to wrapping up the subject. Allow me to ask one or two follow-up questions:
1. There's a chance I may need to insert new columns between "Unit Information". If so, my factor categories would be pushed, e.g., 1 column to the right? Do I need to be aware/modify the code if I were to insert another column between A:G?
2. Similarly, I may need to insert factors under each factor category. It appears all columns/factor are found based on the merged category label in row 1. Just to be certain though, is there anything I need to be aware of when adding another factor to a category?

[jindon]

Change the test sub procedure to

Please Login or Register  to view this content.

[skydivetom]

jindon -- you are truly AMAZING! This works absolutely beautifully! I just tested it by adding another column (as part of unit info). Changed value from 7 to 8 and it executes w/o any issues.

I wish I knew what some of these commands mean (in case someone asks). Would be great to have some comments. But I don't want to abuse your help!

Again, thousand thanks for providing the most superb help I've seen in this VBA forum. You're my hero!

[jindon]

I've just realized "Unit Information".
This will automate everything as long as you merge the cells under the same logic.
1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

[skydivetom]

Wow... excellent... this is even better! Now, looks like I won't have to update the variable CNT if another column is inserted. I'll ensure the the labels in row 1 will be merged.

Totally awesome!!!

[skydivetom]

Argh... sorry, I ran into a critical issue.

While all tabs are properly converted, it appears I now have an issue with creating a pivot table off the individual tabs. Please see attached JPG. Normally, I just select any cell on the converted tab and then click "Insert | Pivot Table".

As depicted in the JPG (Before), the TableRange was automatically populated and w/ "New Worksheet". Clicking "Ok" created the pivot table.

Now, however (see JPG "After"), the table range's name is longer and once I click "Ok", I get the error "Data source reference is not valid". Creating the pivot table off the newly generated tabs is a critical step.

Is this dynamic referencing of the table names causing an issue? Below is the latest VBA (since I inserted some comments and used different names for my categories):

Please Login or Register  to view this content.

PLEASE HELP ON THIS FINAL ISSUE!!

[jindon]

It is late here, so next responce will be tomorrow.

[skydivetom]

Sounds good.... I'll check tomorrow again.

Appreciate your help as I'm stuck now. Again, thank you for help you've already provided. I'm sure there's solution to his pivot table issue.

Have a good one!

[jindon]

Can not replicate that error here.

[alansidman]

Tom

Since you have ignored my post #2. No comment? Does this mean I should ignore any requests you have in the future?

[skydivetom]

alansidman - I apologize... I didn't mean to ignore your post. From my perspective though, your proposed solution did not address my question (i.e. modifying the VBA). Again, I didn't mean to purposefully ignore your comment but I stayed focused on the solution that jindon provided. Thanks for chiming in though.

//

jindon -- I fixed by error by removing the special characters (i.e., "(", ")", and/or "|" from the long/merged category string (row #1). The code now executes successfully and I was able to apply both pivot tables/graphs to the 4 different tabs.

Again, your solution is most flexible and dynamic! THOUSAND THANKS FOR YOUR HELP!!

[AliGW]

Again, didn't mean to ignore it but I stayed focused on the solution that jindon provided.

So you did, in fact, ignore it ... Next time, just acknowledge ALL contributors, even if it's with a "Thanks, but no thanks" message. All the help you get here comes for free - don't take it for granted.

[skydivetom]

jindon ... oh, I have a final question (w/ respect to data management).

Current process (based on example XLS):
1. Example data goes from row 3 through row 20.
2. Once more data is entered, user will have to click the command button in cell A1 in order to execute the VBA (and thus, update pivot tables/graphs).
3. At a minimum, clicking the command button should occur once a **complete** record (e.g., cells A21 through Q21 for 1 record OR A21:Q26 for 5 records) has/have been entered.

My question:
- Given that user will enter data on a regular basis, is there a way to automatically execute VBA once a complete records has been entered (i.e., I could remove the command button)?
- That is, I don't want to invoke Excel's "OnChange" given that it would run the code each time user is typing something into a cell.
- So, ultimately, any automation would have to be associated with a value in the last column (currently column "Q" but it could be column "R" if user were to insert another column under "Unit Information").
- My goal is to ensure all pivot tables/graphs are always updated (in the event user forgets to click the command button).

[skydivetom]

AliGW - copy... will do!

[jindon]

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

so that, it will run anytime you select the worksheet other than "MasterData" sheet.

[skydivetom]

So simple, but sooo powerful! That works great! Again, thank you for all the help, jindon.

Only thing is that it doesn't work once I protect the MasterData worksheet. Urgh.

[jindon]

Only thing is that it doesn't work once I protect the MasterData worksheet. Urgh.

Replace the code with

Please Login or Register  to view this content.

[skydivetom]

Totally sweet... works like a charm. This solution/your help is probably my most favorite thread that I've posted here.

jindon... you're awesome!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[skydivetom]

Question has been marked as "Solved". Reputation has been added.

[skydivetom]

jindon -- I surely do NOT want to abuse your help. If preferred, I'll gladly open another thread.

Quick question though... the VBA currently transposes/converts the master data based on a) unit information and b) factors.

I realize, however, that I want to also use a pivot table in order to address some "demographics" w/o any factor information. Right now, for any data point, each factor column creates a row though so my number get inflated when I merely want to count # of units.

My question: Is there a way I could use a second function that only converts the Unit Information w/o any factors? Is using a second function the best way of doing the conversion? Ultimately though, I would not need to create four different tabs (one for each factor category/merged label) but merely a 5th tab that only includes "unit information".

Thanks,
Tom

[jindon]

I don't understand what you are trying to say...

It may help if you upload a workbook with data and the result that you want.

[AliGW]

Tom - please open a new thread for this. It's a considerably significant extra ask, not just a tweak. Thanks.

[skydivetom]

jindon -- thank you for the prompt response. Per AliGW's recommendation, I started a new thread.

Please see URL below:
https://www.excelforum.com/excel-pro...ml#post5357136

[skydivetom]

jindon - I sent you a private message. Given that I could NOT attached files to the message, I'm posting them here...

Thank you!
Tom

[jindon]

OK, I will have a close look at files.

[skydivetom]

OK, I will have a close look at files.

Thank you for your time/help in advance.

[jindon]

- Naturally, as you already know, once I deactivate the "MasterData" tab and switch to the "Dashboard", I need to 2 new sections be updated on their individually new tabs (e.g., "Trs_Demographics_String" and "Trs_Info_Phase".

How do you want each worksheets?

[skydivetom]

jindon - let's recap:

- Past the UNIT INFORMATION (i.e., I consider it the primary record data), I currently have four sections: 1. INFO_DEMOGRAPHICS; 2. INFO_DESTRUCTIVE_BEHAVIOR; 3. INFO_OFRP; 4. INFO_SCHEDULE
- I now want to insert the 2 new sections (INFO_DEMOGRAPHICS_STRING & INFO_PHASE) into file "PivotTable.xlsm". Ideally, I'll have these 2 new sections inserted directly after the main section UNIT INFORMATION.
- So, the 2 new sections would be located in columns H:K & L:N (see attached JPG as well as the updated "PivotTable v02.xlsm").
- Finally, the process for the 2 new sections should be mimicking the existing process. That is, once new data values on MasterData are entered (or existing values are changed), all *Tables* for all of the 1 UNIT INFORMATION + now 6 support sections are re-generated (per VBA).
- Again, the only thing that's different is that numeric values are counted/summed up while string have should have a count = 1. I don't know if this requires two columns for "Values" in the transposed tables.
- Ultimately though, I need to be able to use the pivot tables to be able to visually/dynamically view the underlying data.

I apologize for the lengthy response but I wanted to make sure I provide you sufficient information for the VBA modification.

Again, THOUSAND THANKS in advance, jindon!

Cheers,
Tom

[jindon]

Your picture is invalid.

Again,

If 2 sections are inserted to maindata, current code will not work, so I need to see the desired result for each worksheets.
Or are you saying, jut insert the sections and the code should produce the result as it is?

[skydivetom]

The main data "UnitInformation" should NOT be changed. So, yes to "just insert the sections and the code should produce the result as it is?"

I merely want to add these 2 new sections. I'm just not clear how to represent the "Value" field in their transposed tables given that string values need to receive the "1" (e.g., I need to be able to count "males" or "females", etc.) while numeric values for e.g. "Value_Alpha" or "Value_Bravo" need to be transposed as is.

I hope this helps.

[jindon]

This code delete if 2 sections are already exists and adds 2 sections to masterdata sheet, so that it is flexible to number of columns to be added.
So, run this code only when you need to.
The rest should not be changed.

Please Login or Register  to view this content.

[skydivetom]

Hmh... not sure exactly how to make that change. Could you please post the spreadsheet so that I can better follow along? I truly appreciate your help/assistance.

Also, please note that I don't need to keep/want the file "NewDataSections.xlsx"... I merely included it to show the new columns. Please see the latest version of the file "PivotTable v02.
Am I missing something?

[jindon]

I merely want to add these 2 new sections.

The code I posted should be run alone.
It is only adding 2 sections to masterdata sheet.

[skydivetom]

Jindon - thank you for the help. I'm completely lost on the changes though. I just posted the latest PivotTable file.

I don't understand how to integrate the new code. Sorry!!

[jindon]

I don't understand how to integrate the new code. Sorry!!

No, it must be run alone.
It is just inserting sections to mastedata and the other code "Deactivate", "Transspose"(not a good name as a sub name though) should remain intact.

[skydivetom]

jindon - I'm not questioning the logic. I'm just curios as to why this is different compared to the other/existing 4 sections.

If I were to see your XLS, it probably would make more sense. Could you pls attach the file?

[jindon]

Here's a book with new code in Module2.

[skydivetom]

jindon:

Wow... this won't be working for me at all. Here's why:

- The source data will will come from a single file. If there's a requirement to place separate the single file into 2 files, it will be complete show stopper.
- For testing purposes, however, I placed the "NewDataSections" file on my desktop.
- I then opened the file PivotTable... I immediately noticed that it doesn't have the four additional tabs (2 "Trs_" tabs + 2 "Pvt_" tabs were missing).
- Anyhow, I went to the MasterData tab (which again did NOT have the 7 new columns (H:N).
- I also checked the "Dashboard" and didn't see anything.

So, unfortunately, this modified process will not work for me and the users. Ultimately, if I were NOT to mix string/numeric data, is there no way to repliced the process for "Unit Information" and apply it to "InfoDemograhicsString" so that I get "1" values for each of the [Race], [Gender], [AgeGroup], [PayGrade] columns?

I really thought your framework was so flexible that I could accomplish this as well? Any additional on how to make it work within the same XLS? For starters, let's focus only on one (1) new section such as "INFO_DEMOGRAPHICS_STRING".

Thank you for any additional assistance in this matter.

Cheers,
Tom

[jindon]

- For testing purposes, however, I placed the "NewDataSections" file on my desktop.

my new code assumes both files are in the same folder.
So, if you run new code, it should open other workbook automatically and add columns to masterdata.

The rest I don't understand what you are trying to say.
I'm out, so wait for someone else.

[skydivetom]

jindon:

I apologize, the last thing I wanted to achieve is to upset/frustrate you. My sincerest apologies!!

I will create a new thread/post and see if there's someone else who can assist.

Based on the most change (see attached XLS), I had hoped the process was simply. That is, if you were to go to "MasterData" tab, you'll see new columns Q:S.
All of their string values are currently transposed onto tab "Trs_Testing".
The only thing that's missing is to change the string values to a "1"... that's really all I had hoped to achieve.

Again, I very much appreciate your help. I don't know if the latest attached will clarify the small modification to the existing code.

Thank you!
Tom

[skydivetom]

jindon -- in case you may be still curious about the scenario, I posted a new question at the URL below:
https://www.excelforum.com/excel-cha...ml#post5427393

For testing/demo purposes only, I simplified the XLS to focus only on "Units", "Demographics", and numeric data ("Section1").

Would love to get you feedback but I certainly understand if you are tired responding to me. Thank you anyways.

Cheers,
Tom