Vba excel как обновить лист

Обновлено: 05.07.2024

Многие, кто работал со сводными таблицами наверняка знают, что при изменении значений в исходных данных(данные, на основании которых создана сводная) сама сводная таблица при этом не обновляется. Чтобы обновить надо проделать еще пару манипуляций:

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh)

Однако, если в конец исходных данных добавить строку(или несколько), то с большой долей вероятности даже обновление сводной таблицы не поможет - добавленная строка не появится в сводной. И чтобы её увидеть необходимо будет изменить источник данных для сводной таблицы, включив новую строку в диапазон. Не очень удобно, не правда ли? Чтобы добиться расширения диапазона исходных данных автоматически вместе с добавлением туда данных, лучше позаботиться об этом до создания сводной таблицы.

Недоавтообновление
Почему "недо" - жать кнопку Обновить все же придется. Но не отчаивайтесь - читайте до конца и мы научимся обновлять все автоматом.
Для счастливых обладателей Excel 2007 и старше есть простой способ без лишних телодвижений. Это встроенный инструмент Таблица (Table) . Его еще иначе называют "умная таблица" и я тоже буду применять этот термин, чтобы не было путаницы.
Умная таблица - это специальный объект, который представляет собой правильную таблицу с заголовками, которая расширяется по мере добавления в неё данных. В ней много еще чего полезного, но нас интересует сейчас именно то, что она расширяется сама по мере добавления данных и что на её основе можно создать сводную таблицу. В нашем случае она будет играть роль динамического именованного диапазона(стандартный именованный диапазон не может быть источником данных для сводной таблицы, поэтому и приходится идти другими путями). Чтобы создать такую таблицу необходимо:

выделить таблицу исходных данных для создания сводной таблицы -перейти на вкладку Вставка (Insert) и выбрать Таблица (Table)
В появившемся окне согласиться с указанным диапазоном или выбрать свой. Галочку Таблица с заголовками (My table has headers) при этом надо обязательно оставить включенной:

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

А дальше все как привыкли:

Выделить любую ячейку исходной таблицы(теперь уже "умной")
Вкладка Вставка (Insert) -группа Таблица (Table) -Сводная таблица (PivotTable)
В диалоговом окне Создание сводной таблицы (Create PivotTable) в пункте Выбрать таблицу или диапазон (Select a table or range) в поле Таблица или диапазон (Range/Table) будет автоматически указан не адрес какого-то диапазона, а имя созданной умной таблицы:

Далее надо определить место размещения Сводной таблицы:

На новый лист (New Worksheet)
На существующий лист (Existing Worksheet)

Готово. Теперь при добавлении строк в эту таблицу для их отображения в сводной достаточно будет лишь обновить сводную таблицу как привыкли. Правда, тут тоже есть нюанс - добавлять строки надо правильно. Можно вбить данные в любую ячейку первой пустой строки таблицы - таблица автоматом расшириться, добавив еще одну строку. Теперь туда можно скопировать нужные данные или добить вручную. Если надо вставить сразу несколько строк - в правом нижнем углу последней строки умной таблицы есть слегка выделяющийся уголочек, который надо ухватить мышью и растянуть на нужное кол-во строк/столбцов.
Если мы просто скопируем строки ниже таблицы, то она не расширится. Это надо учитывать.

Если необходимо настроить на авторасширение уже созданную сводную , то порядок почти такой же, только сводную таблицу создавать не надо. Преобразуем исходные данные в умную таблицу, переходим на лист со сводной таблицей. Выделяем любую ячейку в сводной таблице, переходим на динамическую вкладку Работа со сводными таблицами (PivotTable Tools) -Параметры (Options) -группа кнопок Даныне (Data) -Источник данных (Change data Source) . В появившемся окне в поле Таблица или диапазон (Table/Range) указываем либо ссылку на всю умную таблицу, либо имя нашей умной таблицы(если знаете где его подсмотреть). На что здесь следует обратить внимание: если указывался диапазон, то если он указан верно - в поле вместо адреса ячеек будет отображено имя умной таблицы:

Если же после указания видите именно диапазон - значит что-то указано неверно или таблица не является умной(возможно, форматирование от умной таблицы, но сама умная таблица была удалена).

Полное автообновление
Для полного счастья можно подключить работу макросов. Что я хочу? Я хочу, чтобы как только я изменил/добавил данные в исходные данные - сводная тут же обновилась. Для этого надо сделать следующее:

убеждаемся, что макросы разрешены(Почему не работает макрос?, Что такое макрос и где его искать?)
перейти на лист исходных данных(в моем случае лист так и называется - Исходные данные)
жмем на ярлычке этого листа правой кнопкой мыши -Исходный текст (View code) :
вставляем туда следующий код:

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'проверяем - изменения внутри умной таблицы или нет If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then 'если внутри таблицы, то обновляем сводную таблицу на листе "Автообновляемая сводная" Sheets("Автообновляемая сводная").PivotTables(1).RefreshTable 'для всех сводных на листе ' Dim pt As PivotTable ' For Each pt In Sheets("Автообновляемая сводная").PivotTables ' pt.RefreshTable ' Next End If End Sub

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

Sheets("Автообновляемая сводная") - здесь Автообновляемая сводная это имя листа, на котором расположена сводная таблица. Это очень важно. Если будет указано неверное имя листа - код выдаст ошибку и никакого обновления, конечно же, не произойдет
код сделан таким образом, что на листе исходных данных должна быть только одна умная таблица. Нет, их может быть несколько, но код будет ориентироваться исключительно на первую. И если вы плохо знакомы с принципами создания объектов - то лучше не рисковать. Хотя и здесь можно выйти из положения. Если вы знаете имя своей умной таблицы(его можно подсмотреть на вкладке Конструктор -группа Свойства), то можно изменить код так:
вместо строки
If Not Intersect(Target, Target.Parent.ListObjects( 1 ).Range) Is Nothing Then
записать такую
If Not Intersect(Target, Target.Parent.ListObjects( "Таблица1" ).Range) Is Nothing Then
где Таблица1 - имя вашей умной таблицы, на основании которой создана сводная.
тот же нюанс с листом самой сводной - код ориентирован так, что обновляет только первую сводную на листе Автообновляемая сводная . Здесь так же можно заменить цифру 1 на имя сводной(например .PivotTables("СводнаяТаблица1").RefreshTable ), либо же сделать обновление всех сводных. Для этого надо раскомментировать блок 'для всех сводных на листе (убрать апострофы перед строками) и убрать строку с именем листа, чтобы получилось так:

Private Sub Worksheet_Change(ByVal Target As Range) 'проверяем - изменения внутри умной таблицы или нет If Not Intersect(Target, Target.Parent.ListObjects(1).Range) Is Nothing Then 'если внутри таблицы, то обновляем все сводные на листе "Автообновляемая сводная" Dim pt As PivotTable For Each pt In Sheets("Автообновляемая сводная").PivotTables pt.RefreshTable Next End If End Sub

P.S. Так же можно использовать и иной подход - вставить в модуль листа Автообновляемая сводная такой код:

Private Sub Worksheet_Activate() Me.PivotTables(1).RefreshTable End Sub

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

У меня есть книга с 20 различными сводными таблицами. Есть ли простой способ найти все сводные таблицы и обновить их в VBA?

или, если ваша версия Excel достаточно старый,

этот код VBA обновит все сводные таблицы / диаграммы в книге.

другой непрограммный вариант:

щелкните правой кнопкой мыши на каждой сводной таблице
выберите параметры таблицы
ТИК 'открыть'.
нажмите на кнопку OK

это обновит сводную таблицу при каждом открытии книги.

ActiveWorkbook.RefreshAll обновляет все, не только сводные таблицы, но и запросы ODBC. У меня есть несколько запросов VBA, которые ссылаются на подключения к данным, и с помощью этой опции происходит сбой, поскольку команда запускает подключения к данным без подробностей, предоставленных из VBA

Я рекомендую этот вариант, если вы хотите обновить только пивоты

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

разница? Когда вы создаете новую сводную таблицу, вас спрашивают, Хотите ли вы ее на основе предыдущей таблицы. Если вы скажете нет, эта сводная таблица получает свой собственный кэш и удваивает размер исходных данных. Если вы говорите, Да, вы держите Книга небольшая, но ее можно добавить в коллекцию сводных таблиц, имеющих общий кэш. Вся коллекция обновляется при обновлении любой сводной таблицы в этой коллекции. Таким образом, можно представить себе разницу между обновлением каждого кэша в книге и обновлением каждой сводной таблицы в книге.

в панели инструментов сводной таблицы есть опция обновить все. Этого достаточно. Не нужно делать ничего другого.

у вас сводные таблицы коллекция на VB лист "объект". Таким образом, быстрый цикл, как это будет работать:

Записки из окопов:

не забудьте снять защиту с любых защищенных листов перед обновлением сводной таблицы.
сохранить часто.
Я подумаю о большем и обновлю в свое время. :)

код используется в модуле активировать лист, поэтому он отображает мерцание / сбой при активации листа.

даже мы можем обновить конкретное соединение и, в свою очередь, он обновит все шарниры, связанные с ним.

для этого кода я создал slicer из таблицы, присутствующей в Excel:

я использовал команду, указанную ниже, в недавнем прошлом, и, похоже, она работает нормально.

надеюсь, что это поможет.

Если вы используете MS Excel 2003, то перейдите в вид - >панель инструментов - >сводная таблица из этой панели инструментов мы можем сделать обновление, нажав ! этот символ.

мой инструмент Excel выполняет длинную задачу, и я пытаюсь быть добрым к пользователю, предоставляя отчет о ходе работы в строке состояния или в некоторой ячейке на листе, как показано ниже. Но экран не обновляется или перестает обновляться в какой-то момент (например, 33%). Задача в конечном итоге завершается, но индикатор выполнения бесполезен.

что я могу сделать, чтобы принудительно обновить экран?

Я использую Excel 2003.

добавить функция doevents функция внутри цикла, см. ниже.

вы также можете убедиться, что строка состояния видна пользователю и сбросить ее после завершения кода.

текстовые поля в листах иногда не обновляются когда изменяется их текст или форматирование, и даже команда DoEvent не помогает.

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

следующие команды, кажется, сделать трюк:

вызовите DoEvents в петле.

это повлияет на производительность, поэтому вы можете вызвать его только на каждой, скажем, 10-й итерации.
Однако, если у вас есть только 30, это не проблема.

это не прямой ответ на ваш вопрос вообще, а просто предоставление альтернативы. Я нашел во многих длинных вычислениях Excel большую часть времени ожидания имеет значения обновления Excel на экране. Если это так, вы можете вставить следующий код в передней части вашего субпартнера:

и положить этому конец

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

в частности, если вы имеете дело с пользовательская форма, то вы можете попробовать перекрашивать метод. Вы можете столкнуться с проблемой с функция doevents Если вы используете триггеры событий в вашей форме. Например, любые клавиши, нажатые во время работы функции, будут отправлены функция doevents ввод с клавиатуры будет обработан до обновления экрана, поэтому, если вы меняете ячейки в электронной таблице, удерживая одну из клавиш со стрелками на клавиатура, затем событие изменения ячейки будет продолжать работать до завершения основной функции.

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

в коде UserForm это так же просто, как:

написать DoEvents непосредственно перед строкой, в которой вы обновляете пользовательский интерфейс, он должен работать.

В Microsoft Excel есть много инструментов для сборки данных из нескольких таблиц (с разных листов или из разных файлов): прямые ссылки, функция ДВССЫЛ (INDIRECT) , надстройки Power Query и Power Pivot и т.д. С этой стороны баррикад всё выглядит неплохо.

Но если вы нарвётесь на обратную задачу - разнесения данных из одной таблицы на разные листы - то всё будет гораздо печальнее. На сегодняшний момент цивилизованных встроенных инструментов для такого разделения данных в арсенале Excel, к сожалению, нет. Так что придется задействовать макрос на Visual Basic, либо воспольоваться связкой макрорекордер+Power Query с небольшой "доработкой напильником" после.

Давайте подробно рассмотрим, как это можно реализовать.

Постановка задачи

Имеем в качестве исходных данных вот такую таблицу размером больше 5000 строк по продажам:

Задача: разнести данные из этой таблицы по городам на отдельные листы этой книги. Т.е. на выходе нужно получить на каждом листе только те строки из таблицы, где продажа была в соответствующем городе:

Подготовка

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

Во-первых, создадим отдельную таблицу-справочник, где в единственном столбце будут перечислены все города, для которых нужно создать отдельные листы. Само-собой, в этом справочнике могут быть не все города, присутствующие в исходных данных, а только те, по которым нам нужны отчеты. Проще всего создать такую таблицу, используя команду Данные - Удалить дубликаты (Data - Remove duplicates) для копии столбца Город или функцию УНИК (UNIQUE) - если у вас последняя версия Excel 365.

Поскольку новые листы в Excel по умолчанию создаются перед (левее) текущего (предыдущего), то имеет смысл также отсортировать города в этом справочнике по убыванию (от Я до А) - тогда после создания листы-города расположатся по алфавиту.

Во-вторых, преобразуем обе таблицы в динамические ("умные"), чтобы с ними было проще работать. Используем команду Главная - Форматировать как таблицу (Home - Format as Table) или сочетание клавиш Ctrl + T . На появившейся вкладке Конструктор (Design) назовём их таблПродажи и таблГорода, соответственно:

Способ 1. Макрос для деления по листам

На вкладке Разработчик (Developer) нажмите на кнопку Visual Basic или используйте сочетание клавиш Alt + F11 . В открывшемся окне редактора макросов вставьте новый пустой модуль через меню Insert - Module и скопируйте туда следующий код:

Здесь с помощью цикла For Each . Next реализован проход по ячейкам справочника таблГорода, где для каждого города происходит его фильтрация (метод AutoFilter) в исходной таблице продаж и затем копирование результатов на новый созданный лист. Попутно созданный лист переименовывается в то же имя города и на нем включается автоподбор ширины столбцов для красоты.

Запустить созданный макрос в Excel можно на вкладке Разработчик кнопкой Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 .

Способ 2. Создаем множественные запросы в Power Query

У предыдущего способа, при всей его компактности и простоте, есть существенный недостаток - созданные макросом листы не обновляются при изменениях в исходной таблице продаж. Если обновление "на лету" необходимо, то придется использовать связку VBA+Power Query, а точнее - создавать с помощью макроса не просто листы со статическими данными, а обновляемые запросы Power Query.

Макрос в этом случае частично похож на предыдущий (в нём тоже есть цикл For Each . Next для перебора городов в справочнике), но внутри цикла будет уже не фильтрация и копирование, а создание запроса Power Query и выгрузка его результатов на новый лист:

После его запуска мы увидим те же листы по городам, но формировать их будут уже созданные запросы Power Query:

При любых изменениях в исходных данных достаточно будет обновить соответствующую таблицу правой кнопкой мыши - команда Обновить (Refresh) или обновить сразу все города оптом, используя кнопку Обновить всё на вкладке Данные (Data - Refresh All) .

Читайте также: