I hope this is something that can be done by VBA, but I'm not hopeful as this could have too many variables involved. I will try to explain the best I can.
Part of my role is to bring metrics to management and one of them is our customer surveys. I've built a macro that modifies the report down to the Pivot table level and marked subpar scores. This brings me to my post.
I have a Pivot table with 3 columns and a variable number of rows. This is based on surveys returned so the number could be 3 rows or 60. Column A lists the ticket number and column B lists the average of the scores for each question in the survey. Column C list the technician that did the work. I have to complete Column C by hand because it requires finding the information in our ticketing software and I'm not there yet. So, I will only be showing column A and B in my example.
Column B is my working column for this post. Column B will always contain a value of 1.00 to 5.00. I need to Open a new tab for each Cell that is between 1.00 and 4.00. I do this currently by double clicking on the cell. Then I go in modify the tab and rename it to correspond to the appropriate ticket number. I believe the first half of this should be fairly easy, but I don't know where to start. My VBA skills are "Record Macro". Now we get into the data.
[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Average Of Values[/TD]
[/TR]
[TR]
[TD]Task123758[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]Task123965[/TD]
[TD]4.60[/TD]
[/TR]
[TR]
[TD]Task124682[/TD]
[TD]3.50[/TD]
[/TR]
[TR]
[TD]Inc0125358[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]Inc0126354[/TD]
[TD]1.00[/TD]
[/TR]
</tbody>[/TABLE]
I've included 6 rows to show an example of my data to use. So, in my example I would need to open the Values of 1.00, 4.60, and 3.50. These don't meet our requirement of a 4.01 or better. They require further reporting.
Macro part 1: Open each "average Of Actual Values" that is between "1.00 and 4.00".
Macro part 2: Rename each of these Variable tab names to the corresponding "Row Label" information.
Macro Part 3: Resize all columns on all tabs to maximize every cells width; keeping them only one line tall (no text wrapping).
Macro part 2 is the part that may be impossible. If so, please leave that out and just help with 1 and 3. Hopefully, I'm wrong though and all of this can be done.
Thanks in advance. I hope this is simpler than I think it will be.
Part of my role is to bring metrics to management and one of them is our customer surveys. I've built a macro that modifies the report down to the Pivot table level and marked subpar scores. This brings me to my post.
I have a Pivot table with 3 columns and a variable number of rows. This is based on surveys returned so the number could be 3 rows or 60. Column A lists the ticket number and column B lists the average of the scores for each question in the survey. Column C list the technician that did the work. I have to complete Column C by hand because it requires finding the information in our ticketing software and I'm not there yet. So, I will only be showing column A and B in my example.
Column B is my working column for this post. Column B will always contain a value of 1.00 to 5.00. I need to Open a new tab for each Cell that is between 1.00 and 4.00. I do this currently by double clicking on the cell. Then I go in modify the tab and rename it to correspond to the appropriate ticket number. I believe the first half of this should be fairly easy, but I don't know where to start. My VBA skills are "Record Macro". Now we get into the data.
[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Average Of Values[/TD]
[/TR]
[TR]
[TD]Task123758[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]Task123965[/TD]
[TD]4.60[/TD]
[/TR]
[TR]
[TD]Task124682[/TD]
[TD]3.50[/TD]
[/TR]
[TR]
[TD]Inc0125358[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]Inc0126354[/TD]
[TD]1.00[/TD]
[/TR]
</tbody>[/TABLE]
I've included 6 rows to show an example of my data to use. So, in my example I would need to open the Values of 1.00, 4.60, and 3.50. These don't meet our requirement of a 4.01 or better. They require further reporting.
Macro part 1: Open each "average Of Actual Values" that is between "1.00 and 4.00".
Macro part 2: Rename each of these Variable tab names to the corresponding "Row Label" information.
Macro Part 3: Resize all columns on all tabs to maximize every cells width; keeping them only one line tall (no text wrapping).
Macro part 2 is the part that may be impossible. If so, please leave that out and just help with 1 and 3. Hopefully, I'm wrong though and all of this can be done.
Thanks in advance. I hope this is simpler than I think it will be.
