Function Sh_Exist(iIndex As Double) As Boolean
    Dim wsSh As Worksheet
    On Error Resume Next
    Set wsSh = Sheets(iIndex)
    Sh_Exist = Not wsSh Is Nothing
End Function


Sub main()
Dim rCell As Range, fColumn&, pRow&

Application.DisplayAlerts = False
Application.ScreenUpdating = False


'Dim PT As PivotTable
'Dim PI As PivotItem

'ActiveSheet.Columns(1).NumberFormat = "m/d/yyyy"

n = 1
m = 1

I = ActiveSheet.Cells(n, 1).Value2
j = ActiveSheet.Cells(1, m).Value2

While I <> Empty

n = n + 1

I = ActiveSheet.Cells(n, 1).Value2
    
Wend

While j <> Empty

 m = m + 1

j = ActiveSheet.Cells(1, m).Value2
    
Wend

'        "Лист1!R1C1:R" & n - 1 & "C" & m - 1, Version:=xlPivotTableVersion14).CreatePivotTable _

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Лист1!R1C1:R" & n - 1 & "C" & m - 1, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Лист4!R3C1", TableName:="СводнаяТаблица1", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Лист4").Select
 '   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
 '       "Лист1!R1C1:R" & n - 1 & "C7", Version:=xlPivotTableVersion14).CreatePivotTable _
 '       TableDestination:="Лист4!R3C1", TableName:="СводнаяТаблица1", _
 '       DefaultVersion:=xlPivotTableVersion14
 '   Sheets(1).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Группа МЦ")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("СводнаяТаблица1").AddDataField ActiveSheet.PivotTables _
        ("СводнаяТаблица1").PivotFields("Сумма"), "Контрагенты", xlSum
    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 = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("СводнаяТаблица1").DisplayFieldCaptions = True
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("СводнаяТаблица1").RowGrand = True
    ActiveSheet.PivotTables("СводнаяТаблица1").ColumnGrand = True
    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Город").ShowDetail = False
    Columns(2).Resize(, 40).NumberFormat = "#,##0"


    ActiveSheet.PivotTables("СводнаяТаблица1").CompactLayoutColumnHeader = " Дата"
    ActiveSheet.PivotTables("СводнаяТаблица1").CompactLayoutRowHeader = " Контрагент"
    ActiveSheet.PivotTables("СводнаяТаблица1").DataPivotField.PivotItems("Контрагенты").Caption = " Город"

    Range("B1").Select
    ActiveWindow.FreezePanes = True

    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Группа МЦ"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Группа МЦ")
        .PivotItems("0410").Visible = False
    End With

'=======================преобразование в анализ==============================================================

'=======================1================================
    'Sheets("Лист1").Select
    Sheets("Лист1").Name = "Исходные данные"
    'Sheets("Лист4").Select
    Sheets("Лист4").Name = "Сводная таблица"
    'Sheets("Сводная таблица").Select
    Sheets("Сводная таблица").Copy Before:=Sheets(3)
    'Sheets("Сводная таблица (2)").Select
    Sheets("Сводная таблица (2)").Name = "Анализ"

'=======================2================================


    ActiveSheet.PivotTables("СводнаяТаблица1").RowGrand = True
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Область")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Город").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Контрагент"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("СводнаяТаблица1").CompactLayoutRowHeader = "Область"
    ActiveSheet.PivotTables("СводнаяТаблица1").DataPivotField.PivotItems (" Город")
        Caption = "__________"


   '-------установка в поле выбора нужных цехов-end------------------


    ActiveWorkbook.ShowPivotTableFieldList = False
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False


Range("A1:B1").ClearContents

m = 1

j = ActiveSheet.Cells(4, m).Value2

While j <> Empty

 m = m + 1

j = ActiveSheet.Cells(4, m).Value2
    
Wend

    Range(Cells(5, m + 1), Cells(5, m + 1)).Value = Sheets("Исходные данные").Range("J1:J1")
    Range(Cells(6, m + 1), Cells(6, m + 1)).Value = Sheets("Исходные данные").Range("J2:J2")
    Range(Cells(7, m + 1), Cells(7, m + 1)).Value = Sheets("Исходные данные").Range("J3:J3")
    Range(Cells(8, m + 1), Cells(8, m + 1)).Value = Sheets("Исходные данные").Range("J4:J4")
    Range(Cells(9, m + 1), Cells(9, m + 1)).Value = Sheets("Исходные данные").Range("J5:J5")
    Range(Cells(10, m + 1), Cells(10, m + 1)).Value = Sheets("Исходные данные").Range("J6:J6")
    Range(Cells(11, m + 1), Cells(11, m + 1)).Value = Sheets("Исходные данные").Range("J7:J7")

    'Range(Cells(5, m + 1), Cells(5, m + 1)).FormulaR1C1 = "='Исходные данные'!R[-4] &"C" & 8"
    'Range(Cells(5, m + 1), Cells(5, m + 1)).FormulaR1C1 = "='Исходные данные'!H1"
    'Range(Cells(5, m + 1), Cells(5, m + 1)).AutoFill Destination:=Range(Cells(5, m + 1), Cells(11, m + 1)), Type:=xlFillDefault

    Range(Cells(5, m), Cells(5, m)).FormulaR1C1 = "=RC[1]-RC[-1]"
    Range(Cells(5, m), Cells(5, m)).AutoFill Destination:=Range(Cells(5, m), Cells(11, m)), Type:=xlFillDefault
    Range(Cells(5, m + 2), Cells(5, m + 2)).FormulaR1C1 = "=RC[-3]/RC[-1]"
    Range(Cells(5, m + 2), Cells(5, m + 2)).AutoFill Destination:=Range(Cells(5, m + 2), Cells(11, m + 2)), Type:=xlFillDefault
    Range(Cells(12, m), Cells(12, m)).FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
    Range(Cells(12, m + 1), Cells(12, m + 1)).FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
   'Range(Cells(12, m + 2), Cells(12, m + 2)).FormulaR1C1 = "=AVERAGE(R[-7]C:R[-1]C)"
    Range(Cells(12, m + 2), Cells(12, m + 2)).FormulaR1C1 = "=RC[-3]/RC[-1]"

    Range(Cells(5, m + 2), Cells(12, m + 2)).NumberFormat = "0%"

    Range(Cells(4, m - 1), Cells(4, m - 1)).FormulaR1C1 = "ИТОГО отгружено"
    Range(Cells(4, m), Cells(4, m)).FormulaR1C1 = "Остаток отгрузок"
    Range(Cells(4, m + 1), Cells(4, m + 1)).FormulaR1C1 = "По плану"
    Range(Cells(4, m + 2), Cells(4, m + 2)).FormulaR1C1 = "% выполнения плана"
    Range("A3:B3").ClearContents

    Columns("A:AK").EntireColumn.AutoFit

Range(Cells(4, 1), Cells(12, m + 2)).Select
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    Cells(2, 1).Select
    Range("C1").Select
    
    Sheets("Сводная таблица").Select
    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Группа МЦ"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Группа МЦ")
        .PivotItems("0410").Visible = True
    End With
    Sheets("Лист2").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Лист3").Select
    ActiveWindow.SelectedSheets.Delete
'=================Наччало порошков=========================
Sheets("Анализ").Select
    Rows("12:12").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "Порошки"
    Range("A13").Select
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Лист6").Select
    Sheets("Лист6").Name = "Порошки"
    Sheets("Сводная таблица").Select
    Cells.Select
    Range("B1").Activate
    Selection.Copy
    Sheets("Порошки").Select
    ActiveSheet.Paste
    Range("C1").Select
With ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Группа МЦ")
    For j = 1 To .PivotItems.Count
    If .PivotItems(j).Value <> "0410" Then
        .PivotItems(j).Visible = False
    Else
        .PivotItems(j).Visible = True
    End If
    Next j
    End With
'___________________________________________________________
'___________________________________________________________
'___________________________________________________________

With Sheets("анализ")
For Each rCell In .[b4].Resize(1, .UsedRange.Columns.Count)
    If rCell.Value = "ИТОГО отгружено" Then
        fColumn = rCell.Column - 1
    End If
Next

For Each rCell In .[a4].Resize(.UsedRange.Rows.Count, 1)
    If rCell.Value = "Порошки" Then
        pRow = rCell.Row
    End If
Next
End With
'___________________________________________________________
'___________________________________________________________
'___________________________________________________________

Range("A3").Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Контрагент"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("СводнаяТаблица1").PivotFields("Город").Orientation = _
        xlHidden
With Sheets("Анализ")
.Cells(pRow, 2).Resize(1, fColumn).FormulaR1C1 = _
"=IFERROR(HLOOKUP(SUBSTITUTE(R4C,""ИТОГО отгружено"",""Общий итог""),INDIRECT(""'""&RC1&""'!$B$4:$BB$50""),2,0),"""")"
.Cells(pRow, fColumn + 2).Formula = "=" & .Cells(pRow, fColumn + 3).Address(0, 0) & "-" & .Cells(pRow, fColumn + 1).Address(0, 0)
.Cells(pRow, fColumn + 2).Formula = "=" & .Cells(pRow, fColumn + 3).Address(0, 0) & "-" & .Cells(pRow, fColumn + 1).Address(0, 0)
.Cells(pRow, fColumn + 4).Formula = "=" & .Cells(pRow, fColumn + 1).Address(0, 0) & "/" & .Cells(pRow, fColumn + 3).Address(0, 0)
end with


'------------------------------------------------------------
'' удаляем и скрываем лишние листы
'Sheets("Порошки").Select
'    ActiveWindow.SelectedSheets.Visible = False
'Application.DisplayAlerts = False
'Sheets("Исходные данные").Visible = False
'    Sheets("Сводная таблица").Select
'    Range("C1").Select
'Application.DisplayAlerts = True
''------------------------------------------------------------

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub