Sub Main()
Dim PRange As Range
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim FinalRow As Long
Dim FinalCol As Integer
Dim WBN As Workbook
Dim WSh As Worksheet
  Application.ScreenUpdating = False
    FinalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = Cells(1, Application.Columns.Count).End(xlToLeft).Column
       Set PRange = Cells(1, 1).Resize(FinalRow, FinalCol)
       Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)
         Set PT = PTCache.CreatePivotTable(TableDestination:=Cells(2, FinalCol + 2), TableName:="PivotTable1")
            PT.ManualUpdate = True
            PT.AddFields RowFields:=Array("Менеджер", "Номенклатура"), ColumnFields:="Данные", _
                    PageFields:="Подразделение"
            With PT.PivotFields("Данные1")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 1
                .NumberFormat = "# ##0"
                .Name = "Сумма_1"
            End With
            With PT.PivotFields("Данные2")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 2
                .NumberFormat = "# ##0"
                .Name = "Сумма_2"
            End With
            With PT.PivotFields("Данные3")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 3
                .NumberFormat = "# ##0"
                .Name = "Сумма_3"
            End With
            With PT.PivotFields("Данные4")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 4
                .NumberFormat = "# ##0"
                .Name = "Сумма_4"
            End With
             With PT.PivotFields("Данные5")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 5
                .NumberFormat = "# ##0"
                .Name = "Сумма_5"
            End With
            With PT.PivotFields("Данные6")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 6
                .NumberFormat = "# ##0"
                .Name = "Сумма_6"
            End With
            With PT.PivotFields("Данные7")
                .Orientation = xlDataField
                .Function = xlSum
                .Position = 7
                .NumberFormat = "# ##0"
                .Name = "Сумма_7"
            End With
            PT.NullString = "0"
            PT.ManualUpdate = False
            PT.ManualUpdate = True
Dim PivItem As Object
    'цикл по значениям поля Подразделение
  For Each PivItem In PT.PivotFields("Подразделение").PivotItems
    PT.PivotFields("Подразделение").CurrentPage = PivItem.Name
      'пересчитать сводную таблицу
      PT.ManualUpdate = False
      PT.ManualUpdate = True
      Set WBN = Workbooks.Add(xlWBATWorksheet)
      Set WSh = WBN.Worksheets(1)
          WSh.Name = PivItem.Name
          'копируем диапазон соответствующего Подразделения
          PT.TableRange2.Offset(3, 0).Copy
          WSh.Range("A2").PasteSpecial xlPasteValuesAndNumberFormats
          WSh.Columns("A:I").AutoFit
          WBN.SaveAs Filename:=ThisWorkbook.Path & "\" & WSh.Name & ".xls"
          WBN.Close SaveChanges:=True
  Next
  Application.ScreenUpdating = True
End Sub