макрос для сводной таблицы в excel

[Olegspb]

Пытаюсь написать макрос, который создавал бы сводную таблицу на основе таблицы данных. Проблема в том, что границы таблицы данных  в макросе получаются фиксированными, а ведь они будут меняться. Как сделать, чтобы границы получались автоматически? На данный момент получается следующее:
Sub СводнаяТаблица()
       ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Лист1 '!R1C1:R1680C5").CreatePivotTable TableDestination:="", TableName:= _
        "СводнаяТаблица1"
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("СводнаяТаблица1").SmallGrid = False
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Кредитор")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Имя поставщика")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Сумма")
        .Orientation = xlDataField
        .Position = 1
    End With
End Sub

[HandKot]

как автоматически не знаю, но можно менять источник руками
примерно так

Код:

Public Sub change_source()
    Dim pv As PivotTable
    
    Set pv = ActiveSheet.PivotTables("СводнаяТаблица1")
    
    pv.PivotCache.SourceData = "Лист1!R1C1:R10C5"
    pv.PivotCache.Refresh
End Sub

[Olegspb]

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

[HandKot]

а что вы понимаете под термином "граница"?

[HandKot]

Olegspb если я правильно понял, то пусть диапозон данных имел значения

Код:

Лист1!R1C1:R10C5

, потом в данные добавили еще 15 строк, тогда получаем

Код:

     Dim pv As PivotTable
   
    Set pv = ActiveSheet.PivotTables("СводнаяТаблица1")
   
    pv.PivotCache.SourceData = "Лист1!R1C1:R25C5"
    pv.PivotCache.Refresh

попробуйте

[Olegspb]

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

[HandKot]

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

Как часто и почему(отчего) меняются данные

[RomCom]

Наверно примерно так:

Код:

pv.PivotCache.SourceData = "Лист1!A1:" & ActiveCell.SpecialCells(xlLastCell).Address

[Olegspb]

Стою на освальте в лыжи обутый. То ли лыжи не едут, то ли я ...ый. Не получается ничего.
To HandKot
Данные в информационной системе обновляются постоянно. Я периодически делаю отчеты для своей службы, поэтому объем данных всегда разный. Из-за этого придется всегда корректировать адрес диапазона. Что есть Элементарное КЮ.
(С)Кин-дза-дза

[HandKot]

Что есть Элементарное КЮ.

в том то и дело, что элементарное

я так и не понял как у вас происходит работа с листом Excell, но можно написать примерно так
исходные данные:
1) есть некий лист
2) есть запрос или некая программа, которые формируют данные для сводной таблицы

варианты решения:
1) после того, как обновили запрос ( сам-то он никогда не заработает, надо пинать его), запустить макрос, который и изменяет диапозон данных для сводной таблицы
2) либо в событиях листа обрабатывать изменение дитапозона данных для сводной таблицы (может притормаживать, если изменения в листе происходят часто)

Я периодически делаю отчеты для своей службы, поэтому объем данных всегда разный

вот коли вы делаете отчет для своей службы, то после (или до) точно не скажу и меняйте макросом источник данных (очень неплохую идею RomCom предложил)

[Olegspb]

to HandKot
Идею RomCom понимаю и вроде бы все правильно, но выдается ошибка:"Недопустимое имя поля сводной таблицы ...". Хотя все заданно правильно.

[HandKot]

Попробуйте в окне дебагера выполнить

Код:

 ? "Лист1!A1:" & ActiveCell.SpecialCells(xlLastCell).Address 

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

[RomCom]

Попробуй так:

Код:

"Лист1!A1:" & Worksheets("Лист1").Cells.SpecialCells(xlLastCell).Address 

[Olegspb]

To HandKot and RomCom
Большое спасибо за помощь. Макрос работает.

[Olegspb]

Нашел еще один способ:

Worksheets("Лист1").Activate
selection.CurrentRegion.Select
numRows = selection.Rows.Count
numColumns = selection.Columns.Count
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Лист1!A1:" & Cells(numRows, numColumns).Address).CreatePivotTable TableDestination:="", TableName:= _
        "СводнаяТаблица1"
и т.д.

Может кому-нибудь понадобится. :razz: