Решение найдено следующим способом:
Код |
---|
Sub Ñâîäíàÿ() ' ' Ñâîäíàÿ Ìàêðîñ '' Sheets("ïîâàãîííàÿ îòãðóçêà").Select Sheets("ïîâàãîííàÿ îòãðóçêà").Copy After:=Sheets(4) Dim r As Range With ActiveSheet Set r = Intersect(.UsedRange, .[u:u]).Offset(1) r.FormulaLocal = r.FormulaLocal End With '----------------------------------------------------------------------------- Sheets("Ñâîäíàÿ").Select Cells.Select Selection.Delete Shift:=xlUp Range("A1").Select ' ôîðìèðóåì ñâîäíóþ òàáëèöó ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "ïîâàãîííàÿ îòãðóçêà (2)!R3C1:R65000C26", Version:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Ñâîäíàÿ!R1C1", TableName:= _ "ÑâîäíàÿÒàáëèöà12", DefaultVersion:=xlPivotTableVersion10 Sheets("Ñâîäíàÿ").Select Cells(1, 1).Select With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà12").PivotFields("PID") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà12").AddDataField ActiveSheet. _ PivotTables("ÑâîäíàÿÒàáëèöà12").PivotFields("Äàòà îòãðóçêè"), _ "Êîëè÷åñòâî ïî ïîëþ Äàòà îòãðóçêè", xlCount With ActiveSheet.PivotTables("ÑâîäíàÿÒàáëèöà12").PivotFields( _ "Êîëè÷åñòâî ïî ïîëþ Äàòà îòãðóçêè") .Caption = "Ìàêñèìóì ïî ïîëþ Äàòà îòãðóçêè" .Function = xlMax .NumberFormat = "ÄÄ.ÌÌ.ÃÃÃÃ" End With Range("b1:b43").Select Selection.NumberFormat = "m/d/yyyy" Range("A1").Select End Sub |