Как автоматически удалять листы с детализацией
Автор Дмитрий Якушев На чтение4 мин. Просмотров42
Что делает макрос: Одной из самых привлекательных особенностей сводной таблицы является то, что она дает вам возможность при помощи двойного щелчка мышью по ряду просмотреть детали. Подробности выводятся на новый лист, который вы можете просмотреть. В большинстве случаев, вы не хотите, чтобы эти листы были, так как становятся помехой, заставляя вас тратить время, чтобы очистить их.
Особенно это становится проблемой, когда вы распространяете отчеты сводных таблиц для пользователей, которые часто смотрят детали. Нет никакой гарантии, что они вспомнят, что нужно очистить листы. Хотя эти листы, вероятно, не вызовут проблемы, они могут загромождать рабочую книгу.
Этот макрос поможет автоматически удалять листы с детализацией сводной.
Содержание
- Как макрос работает
- Код макроса
- Как этот код работает
- Код макроса
- Как это код работает
- Как использовать
Как макрос работает
Основная предпосылка этого макроса на самом деле очень простая. Когда пользователь нажимает кнопку для деталей, макрос просто переименовывает выходной лист так, чтобы первые десять символов являлись PivotDrill. Затем рабочая книга закрывается, макрос находит любой лист, который начинается с PivotDrill и удаляет его.
Реализация действительно немного сложнее, потому что вы по существу должны иметь две части кода. Одна часть идет в случае Worksheet_BeforeDoubleClick, в то время как другая часть переходит в событие Workbook_BeforeClose.
Код макроса
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 |
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'Шаг 1: объявляем переменные
Dim pt As String
'Шаг 2: Выход, если Двойной щелчок по ячейке, не связанной со сводной 'таблицей
On Error Resume Next
If IsEmpty(Target) And ActiveCell.PivotField.Name <> "" Then
Cancel = True
Exit Sub
End If
'Шаг 3: Установите объект PivotTable
pt = ActiveSheet.Range(ActiveCell.Address).PivotTable
'Шаг 4: Вывести детализацию
If ActiveSheet.PivotTables(pt).EnableDrilldown Then
Selection.ShowDetail = True
ActiveSheet.Name = _
Replace(ActiveSheet.Name, "Лист", "PivotDrill")
End If
End Sub |
Как этот код работает
- Шаг 1 начинается с создания переменной объекта PT для нашей сводной таблицы.
- Шаг 2 проверяет двойной щелчок ячейки. Если ячейка не связана с какой-либо сводной таблицы, мы отменяем событие двойного щелчка.
- Если в сводной таблице действительно связана с ячейкой, Шаг 3 заполняет переменную PT с сводной таблицы.
- И, наконец, шаг 4 проверяет свойство EnableDrillDown. Если он включен, мы вызываем метод ShowDetail. Это выводит детали детализации вплоть до нового листа.
Макрос переименовывает выходной лист так, чтобы первые десять символов были PivotDrill. Мы делаем это с помощью функции Replace. Функция Заменить заменяет определенный текст в выражении с другим текстом. В этом случае мы заменяем слово лист с PivotDrill: Replace (ActiveSheet.Name, «Лист», «PivotDrill»). Лист1 становится PivotDrill1; Лист12 становится PivotDrill12, и так далее.
Затем макрос создает событие Worksheet_BeforeDoubleClick. Как следует из названия, этот код работает, когда рабочая книга закрывается.
Код макроса
|
1
2
3
4
5
6
7
8
9
10
11
12
13 |
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Шаг 5: Объявляем переменные
Dim ws As Worksheet
'Шаг 6: Проходим через рабочие листы
For Each ws In ThisWorkbook.Worksheets
'Шаг 7: Удаляем любой лист, который начинается с Pivot Drill
If Left(ws.Name, 10) = "PivotDrill" Then
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End If
Next ws
End Sub |
Как это код работает
- Шаг 5 объявляет переменную WS рабочего листа. Это используется для хранения объектов рабочего листа.
- Шаг 6 начинает цикл, говоря Excel, что мы хотим оценить все рабочие листы в этой книге.
- На последнем этапе мы оцениваем имя листа, который имеет фокус в цикле. Если левые десять символов этого имени листа PivotDrill, мы удаляем рабочий лист. После того, как все листы были оценены, все Детализации были очищены, макрос останавливается.
