Автоматически удалять листы с детализацией

Одной из особенностей сводной таблицы является то, что она дает возможность при помощи двойного щелчка мышью просмотреть детали. Подробности выводятся на новый лист, который вы можете просмотреть. В большинстве случаев, эти листы становятся помехой – нужно тратить время, чтобы удалить их.

Особенно это становится проблемой, когда вы отправляете отчеты сводных таблиц для пользователей, которые часто смотрят детали. Нет никакой гарантии, что они вспомнят, что нужно очистить листы. Эти листы могут загромождать рабочую книгу. Этот макрос поможет автоматически удалять листы с детализацией.

Этот макрос переименовывает лист с детализацией (во время его создания), добавляя перед именем «PivotDrill». Когда рабочая книга закрывается, макрос находит любой лист, который начинается с «PivotDrill» и удаляет его. Для этого нужно создать два кода.

Одна часть будет работать с событием Worksheet_BeforeDoubleClick, а другая с событием Workbook_BeforeClose. 

1. Шаг 1 начинается с создания переменной объекта PT для нашей сводной таблицы. 

2. Шаг 2 проверяет двойной щелчок ячейки. Если ячейка не связана с какой-либо сводной таблицей, мы отменяем событие двойного щелчка. 

3. Если ячейка действительно связана со сводной таблицей, то Шаг 3 заполняет переменную PT данной сводной таблицей. 

4. И, наконец, шаг 4 проверяет свойство EnableDrillDown. Если оно включено, мы вызываем метод ShowDetail. Это выводит детали нового листа. 

Макрос переименовывает новый лист так, чтобы первые десять символов были PivotDrill. Мы делаем это с помощью функции Replace. Функция Replace заменяет определенный текст другим текстом. В этом случае мы заменяем слово «Лист» на «PivotDrill»: Replace (ActiveSheet.Name, «Лист», «PivotDrill»). Например, Лист1 становится PivotDrill1; Лист12 становится PivotDrill12, и так далее. 

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль: 

1. Активируйте редактор Visual Basic, нажав ALT + F11. 
2. Щелкните правой кнопкой мыши на имя проекта / рабочей книги в окне проекта. 
3. Выберите Insert➜Module. 
4. Введите или вставьте код. 
5. Выберите событие BeforeDoubleClick из раскрывающегося списка событий. 
6. Введите или вставьте код.

Следующий макрос работает, перед тем как рабочая книга закрывается.

5. Шаг 5 объявляет переменную WS для хранения объектов рабочего листа. 

6. Шаг 6 начинает цикл, говоря Excel, что мы хотим оценить все рабочие листы в этой книге. 

7. На последнем этапе мы оцениваем имя каждого листа. Если левые десять символов этого имени PivotDrill, мы удаляем рабочий лист. После того, как все листы были оценены и все листы с Детализацией были удалены, макрос останавливается. 

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

1. Активируйте редактор Visual Basic, нажав ALT + F11.

2. Щелкните правой кнопкой мыши на имя проекта / рабочей книги в окне проекта.

3. Выберите Insert➜Module.

4. Введите или вставьте код.

5. Выберите событие Workbook_BeforeClose из раскрывающегося списка событий.

6. Введите или вставьте код.

Файл доступен только для учеников и подписчиков нашего клуба.

Подробная информация о клубе и условия членства доступны по ссылке