'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.

excel powerpivot

Solution 1:^[1]

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:

https://www.google.com/search?q=find+last+pivot+table+row

Solution 2:^[2]

I came across this issue lately, and built the following. Of course to have it work, the prerequisite must be met that in principle the two tables do fit on one sheet. What I than do, is build in sufficient space (=rows) between the two tables, and after refresh hide the rows that are left between the tables. So it does require VBA, and can for example be executed on the PivotTable_Update event.

Private Sub hideRowsBetweenListObjects(sheetName As String)
Dim tblRowPosition1 As Integer
Dim tblNrOfRows1 As Integer
Dim tblRowPosition2 As Integer
Dim tblNrOfRows2 As Integer

'Initialize
Application.ScreenUpdating = False

With Worksheets(sheetName).ListObjects(1)
    tblRowPosition1 = .Range.Row
    tblNrOfRows1 = .Range.Rows.Count
End With

With Worksheets(sheetName).ListObjects(2)
    tblRowPosition2 = .Range.Row
    tblNrOfRows2 = .Range.Rows.Count
End With

With Worksheets(sheetName)
    If tblRowPosition1 < tblRowPosition2 Then
        .Range(.Cells(tblRowPosition1 + tblNrOfRows1, 1), .Cells(tblRowPosition2 - 4, 1)).EntireRow.Hidden = True
    ElseIf tblRowPosition2 < tblRowPosition1 Then
        .Range(.Cells(tblRowPosition2 + tblNrOfRows2, 1), .Cells(tblRowPosition1 - 4, 1)).EntireRow.Hidden = True
    End If
End With

End Sub

Solution 3:^[3]

If you are using a Professional version of excel you can use powerview. to create dashboards. Wenn using powerview you can limit the space a pivottable uses.

see below links for more information:
how to activate powerview

microsoft tutorial on powerview

excel powerview combined with sharepoint

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	curtisp
Solution 2	Freddy
Solution 3	Tom

'Excel 2013 : multiple pivot tables on one sheet (BELOW) each other

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]