Excel 2013 : multiple pivot tables on one sheet (BELOW) each other
I need to create dashboard with multiple pivot table in the same excel sheet one below the other.
The problem is that When the Pivot Table refreshes it may be longer (more rows) so it gives a warning that the rows below what it needs will be overwritten.
I'd like to know how to configure Excel pivot table for adding row in pivot table without overwrite the following.
I have already seen an example when Microsoft Techdays 2013 but I can not remember the method. (There is a check box to enable in Excel 2013)
Thank you.
Answer
Whenever I have had more than one pivot table per worksheet I either
1) restrict the number of rows a pivot table eg I had report that need rolling 6 month values so I always had 6 rows but the values advanced each month. In this case you need to use VBA to 'check' and 'uncheck' the values that show. Use methods such as those found in this Google search to do this:
https://www.google.com/search?q=find+last+used+row#q=excel+pivot+table+vba+to+filter+row+values
2) upon each refresh, recreate each pivot table from scratch, positioning each pivot table accordingly, by deleting existing pivot tables, and then recreating pivot tables from top down. For this method you need to determine which is last row in pivot table so you can recreate next one below it. Use methods such as those in Google search to find the last pivot table row: