Archie Goodwin

Сводная таблица удобный инструмент для формирования динамических отчетов (многоразовых). Более того, гибкости таким отчетам добавляют срезы, которые можно добавлять к сводным.

Но минус в том, что когда отпускаешь все фильтры и сводная растягивается максимально по строкам, она не добавляет новые строки на листе экселя, а движется поверх них замещая все что там есть.

Это вообще не страшно, когда у вас всего одна сводная, но становится проблемой, когда вы на одном листе хотите разместить несколько сводных одна по другой.

Так вот, когда верхняя сводная попытается залезть на територию нижней (загрузлось больше данных или отпустили все фильтры), то excel выдаст ошибку что-то типа «запрещено перекрытие отчетов сводных», то есть две сводные не могут размещаться на одних и тех же ячейках.

Во вложении решение этой проблемы через макрос.

После первой сводной есть большой пропуск строк – запас в 1000 строк, которые используются как буфер. Макрос отслеживает любое изменение сводной на листе и мгновенно пересматривает кол-во используемых строк сводной в заданном диапазоне, после чего прячет неиспользуемые строки. Так визуально две сводные находятся рядом возле друг друга и при этом решается проблема того, что одна сводная может залезть на другую.

Макрос сам размещается в коде листа, а не модуля - Жмакнуть правой кнопкой мыши на требуемый лист экселя слева внизу на панели листов документа / В контекстном меню выбрать "Просмотреть код" - сюда и размещать макрос.

Сам макрос с максимально детализированными комментариями:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'отслеживаем изменение данных в сводных таблицах на листе

'Выключаем обновление экрана для ускорения
Application.ScreenUpdating = False

Dim lRow As Long

'открываем скрытые строки
Rows("15:1015").EntireRow.Hidden = False

'Диапазон для отслеживания пустых строк
Range("A14:A1015").Select
'Определяем последнюю используемую строку в заданном диапазоне
lRow = Selection.Find(What:="*", _
 LookAt:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Row

lRow = lRow + 1

'Прячем лишние строки
Rows(lRow & ":1015").EntireRow.Hidden = True

Range("A14").Select
'Диаграмме приндительно меняем высоту, на случай если был какой=то сдвиг в данных
ActiveSheet.Shapes("Диаграмма 2").Height = 283.4645669291

'Включаем обновление экрана
Application.ScreenUpdating = True

End Sub