Sub u_744() Application.ScreenUpdating = False For c = 2 To Sheets.Count Sheets(c).Rows("1").Insert Shift:=xlDown Sheets(c).Range("a1:p1") = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16) a = Sheets(c).Cells(Rows.Count, "d").End(xlUp).Row d = Sheets(c).Name Set b = Sheets(c).Range("a1:p" & a) ActiveWorkbook.PivotCaches.Create(xlDatabase, "'" & d & "'!A1:P" & a).CreatePivotTable "'" & d & "'!R9C18", TableName:="j_" & c With Sheets(c).PivotTables("j_" & c).PivotFields("4") .Orientation = xlRowField .Position = 1 End With With Sheets(c).PivotTables("j_" & c).PivotFields("14") .Orientation = xlRowField .Position = 2 End With Sheets(c).PivotTables("j_" & c).AddDataField Sheets(c).PivotTables("j_" & c). _ PivotFields("14"), "u", xlCount With Sheets(c).PivotTables("j_" & c).PivotFields("u") .Caption = "Сумма" .Function = xlSum End With With Sheets(c).PivotTables("j_" & c).PivotFields("4") For j = 1 To .PivotItems.Count k = .PivotItems(j).Value If k = " ЗАРАБОТНАЯ ПЛАТА" _ Or k = " МАТЕРИАЛЫ" _ Or k = " ОХР/ОПР, ПЛАНОВАЯ ПРИБЫЛЬ" _ Or k = " ТРАНСПОРТ" _ Or k = " ЭКСПЛУАТАЦИЯ МАШИН" Then .PivotItems(j).Visible = True Else .PivotItems(j).Visible = False End If Next End With Next Application.ScreenUpdating = True End Sub
[/vba]
[vba]
Код
Sub u_744() Application.ScreenUpdating = False For c = 2 To Sheets.Count Sheets(c).Rows("1").Insert Shift:=xlDown Sheets(c).Range("a1:p1") = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16) a = Sheets(c).Cells(Rows.Count, "d").End(xlUp).Row d = Sheets(c).Name Set b = Sheets(c).Range("a1:p" & a) ActiveWorkbook.PivotCaches.Create(xlDatabase, "'" & d & "'!A1:P" & a).CreatePivotTable "'" & d & "'!R9C18", TableName:="j_" & c With Sheets(c).PivotTables("j_" & c).PivotFields("4") .Orientation = xlRowField .Position = 1 End With With Sheets(c).PivotTables("j_" & c).PivotFields("14") .Orientation = xlRowField .Position = 2 End With Sheets(c).PivotTables("j_" & c).AddDataField Sheets(c).PivotTables("j_" & c). _ PivotFields("14"), "u", xlCount With Sheets(c).PivotTables("j_" & c).PivotFields("u") .Caption = "Сумма" .Function = xlSum End With With Sheets(c).PivotTables("j_" & c).PivotFields("4") For j = 1 To .PivotItems.Count k = .PivotItems(j).Value If k = " ЗАРАБОТНАЯ ПЛАТА" _ Or k = " МАТЕРИАЛЫ" _ Or k = " ОХР/ОПР, ПЛАНОВАЯ ПРИБЫЛЬ" _ Or k = " ТРАНСПОРТ" _ Or k = " ЭКСПЛУАТАЦИЯ МАШИН" Then .PivotItems(j).Visible = True Else .PivotItems(j).Visible = False End If Next End With Next Application.ScreenUpdating = True End Sub