Hi Buzza,
Sorry if I'm not helping.
I just want to give an idea that if it's me,
I prefer to have another sheet with a blank table rather than create a new table based on the sheet "Raw Data".
So, what I do is something like this :
Assuming that in sheet "Raw Data", the data which is going to be a table is always starts from column B to F row 12,
I make a dynamic named range in "Name Manager" of Excel, where in the reference range box is a formula like this :
Say I name that range is "InputData".
Next, I prepare a sheet (say, sheet XXX), with a table with one blank one row :
EXCEL_2020-05-11_17-39-03.png
Say, I name the table "DataTable". This is still a blank table.
Next, I prepare the pivot table based on "DataTable" table, say on sheet YYY.
Design the pivot table to whatever I like,
have which header name is the ReportFilter, which one is the Row Labels, etc. For example like this :
EXCEL_2020-05-11_18-02-39.png
This is still a blank pivot table. Say I name it as "ptMain".
Then have a vba where the process is :
1. clear the "DataTable" table (which is in Sheet XXX) from cell A2 to the last data
2. copy "InputData" range (where the range is in Sheet Row Data)
3. paste it to cell A2 of "DataTable" in Sheet XXX ...
the "DataTable" table now look like this :
EXCEL_2020-05-11_17-54-12.png
4. fill the rest of the header of "DataTable" with the formula as needed.
the "DataTable" table now look like this :
EXCEL_2020-05-11_17-57-14.png
5. refresh the "ptMain" pivot table (which is in Sheet YYY).
6. then do the code to whatever I want to see in "ptMain" pivot table.
Here is the code example to do number 1 to 5 :
Bookmarks