VBA и сводная таблица

[Excel (VBA) Сводные таблицы. Создание. Обновление. / Visual Basic / Sql.ru] хорошо, посмотри! http://www.sql.ru/forum/236836/excel-vba-svodnye-tablicy-sozdanie-obnovlenie

Sub SvodTable()
Dim i As Integer
Dim s As String

Sheets("Работы").Activate
Sheets("Работы").Unprotect
' определяю диапазон исходной таблицы  
ActiveSheet.Unprotect
ActiveSheet.Cells(2, 2).Select
  i = 2
      While ActiveSheet.Cells(i, 2).Formula <> ""
           i = i + 1
      Wend
'Range(Cells(2, 2), Cells(i - 1, 16)).Select
s = "Работы!" & Range(Cells(2, 2), Cells(i - 1, 16)).Address(ReferenceStyle:=xlR1C1)

' на основе диапазона строю сводную (в этой строке вылетает ошибка -Недопустимое имя сводной таблицы..)
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        s).CreatePivotTable TableDestination:="", TableName:= _
        "СводнаяТаблица1", DefaultVersion:=xlPivotTableVersion10

    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("СводнаяТаблица1").AddFields RowFields:=Array( _
        "ФИО сотрудника", "Наименование услуги", "Данные"), ColumnFields:="Месяц"
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Итого, руб.")
        .Orientation = xlDataField
        .Caption = "Сумма по полю Итого, руб."
        .Position = 1
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Итого, у.е.")
        .Orientation = xlDataField
        .Caption = "Сумма по полю Итого, у.е."
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = True
End Sub

Можно так.

Dim i As Long, strSource$ 

i = [a1].End(xlDown).row strSource = "'" & ActiveSheet.Name & "'!" & Range(Cells(1, 1), Cells(i, 3)).Address(ReferenceStyle:=xlR1C1)
Sheets.Add.Name = "Table1" ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _  

     strSource, VERSION:=xlPivotTableVersion15).CreatePivotTable _ 

     TableDestination:="Table1!R1C1", TableName:="СводнаяТаблица", _ DefaultVersion:=xlPivotTableVersion15

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

Sub CreatePivotTable()   
Dim PTCache As PivotCache
Dim PT As PivotTable

'  Создание  области  кэша    

Set PTCache = ActiveWorkbook.PivotCaches.Create( _

       SourceType:=xlDatabase, _ 

       SourceData:=Range("A1").CurrentRegion) 

'Добавление  нового  листа  в  сводную  таблицу    

Worksheets.Add 

Создание  сводной  таблицы    

Set PT = ActiveSheet.PivotTables.Add( _

       PivotCache:=PTCache, _

       TableDestination:=Range("A3")) 

Определение  полей    

With PT       

.PivotFields("Регион").Orientation = xlPageField       

.PivotFields("Месяц").Orientation = xlColumnField       

.PivotFields("Торговый  представитель") _          

             .Orientation = xlRowField       

.PivotFields("Продажи").Orientation = xlDataField       

'заголовки  полей  отсутствуют       

.DisplayFieldCaptions = False    

End With 

End Sub 

В данном случае процедура  CreatePivotTable  была упрощена (что облегчило ее  понимание) благодаря объявлению двух  переменных объекта:  PTCache  и  PT. Новый объект PivotCache  был создан с  помощью метода  Create. Также был добавлен рабочий лист, который стал  активным (на этом  листе размещается сводная таблица). Затем был создан объект  PivotTable  с  помощью метода  Add  из коллекции  PivotTables. В  последнем фрагменте кода добавляются поля в  сводную таблицу,  а  также задается их положение в  таблице путем присвоения значения свойству  Orientation. Обратите внимание  на  то, что исходный макрос  жестко  привязан  к  диапазону данных, на  основе которого  создается объект  PivotCache  ('Лист1!R1C1:R13C4'), и  к местоположению сводной таблицы (Лист2). В  процедуре  CreatePivotTable  сводная таблица основана на  текущем диапазоне, окружающем ячейку A1. Это гарантирует, что макрос будет выполняться даже тогда, когда в диапазон  добавлены дополнительные данные. Добавление рабочего  листа до  того, как была создана сводная таблица, исключает необходимость жесткого кодирования ссылки на  лист. Еще одно отличие заключается в  том, что написанный вручную  макрос  не  определяет  имя сводной таблицы. Да  это и  не  требуется, поскольку используется переменная объекта  PT, которая выполняет  эту задачу.