I'm attempting to automate a daily excel report procedure with vba and am in over my head (just started exploring macros and vba this year).
We have a pivot as shown below. We drilldown selectively based on rather data populates a current or previous month cell.
For the current month we only drill Apples and Oranges, but in all previous months we also drill Bananas and Grapes. Each day there could be any combination of current and previous months, and any combination of fruits from among those listed below.
So in the above example pivot we would want to end up with 6 tabs... Jan Apples, Mar Bananas, Mar Grapes, Mar Oranges, Apr Apples, Apr Oranges.
Also, if possible I'd love the tabs to be named a composite of the row and column Labels they came from [Month Fruit].
I've thought of a few possible, but inelegant, ways of achieving this outcome and I'm frankly overwhelmed trying to even devise a plan.
I could drill any cell with a value and delete empty or unneeded tabs, I'm guessing based on If/else comparisons between the month heading and current date and a set of predefined row headings (i.e. if current month apples & oranges, else apples/oranges/grapes/bananas).
I could dim row and column count variables and combine them with the above if/else statements to only create the tabs I need regardless of what months and fruits are present that day.
Maybe there's some way to drill based on predefined combinations of row and column headings regardless of how the pivot is filled each day? I.e. 'Current month Apples - if present with data', 'Previous month Grapes - if present with data', etc.
Took a couple weeks, but finally made something that does what I wanted. More or less. 8-)