Excel pivot macro

Excel pivot macro
2019-10-15T12:27:28+02:00
2019-10-15T14:03:02+02:00
2022-10-15T21:40:58+02:00
alexzjeah
Sziasztok! Segítséget szeretnék kérni, mégpedig azért, mert létrehoztam egy Pivot macrot. Viszont Run-time error "5" hibakódot kapok, és nem tudok rájönni, hogy miért. Forrást csatolom.

Sub Pivotalas() ' ' Pivotalas Macro ' Pivotalas_SCO ' ' Range("A1:BM1").Select Range(Selection, Selection.End(xlDown)).Select Range("A1:BM1500").Select Sheets.Add 'Create Pivot Cache from Source Data Set pvtCache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=SrcData) 'Create Pivot table from Pivot Cache Set pvt = pvtCache.CreatePivotTable( _ TableDestination:=StartPvt, _ TableName:="PivotTable1") Sheets("Sheet3").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1") .ColumnGrand = True .HasAutoFormat = True .DisplayErrorString = False .DisplayNullString = True .EnableDrilldown = True .ErrorString = "" .MergeLabels = False .NullString = "" .PageFieldOrder = 2 .PageFieldWrapCount = 0 .PreserveFormatting = True .RowGrand = True .SaveData = True .PrintTitles = False .RepeatItemsOnEachPrintedPage = True .TotalsAnnotation = False .CompactRowIndent = 1 .InGridDropZones = False .DisplayFieldCaptions = True .DisplayMemberPropertyTooltips = False .DisplayContextTooltips = True .ShowDrillIndicators = True .PrintDrillIndicators = False .AllowMultipleFilters = False .SortUsingCustomLists = True .FieldListSortAscending = False .ShowValuesRow = False .CalculatedMembersInFilters = False .RowAxisLayout xlCompactRow End With With ActiveSheet.PivotTables("PivotTable1").PivotCache .RefreshOnFileOpen = False .MissingItemsLimit = xlMissingItemsDefault End With ActiveSheet.PivotTables ("PivotTable1") With ActiveSheet.PivotTables("PivotTable1").PivotFields("Final SSD week") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Final SSD") .Orientation = xlRowField .Position = 2 End With ActiveSheet.PivotTables("PivotTable1").PivotFields("Final SSD").AutoGroup With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area") .Orientation = xlRowField .Position = 5 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("ORG NAME") .Orientation = xlRowField .Position = 6 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSTOMER/END CUSTOMER" _ ) .Orientation = xlRowField .Position = 7 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Order") .Orientation = xlRowField .Position = 8 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Class") .Orientation = xlRowField .Position = 9 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("PID") .Orientation = xlRowField .Position = 10 End With ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Net Open Qty"), "Sum of Net Open Qty", xlSum ActiveWorkbook.ShowPivotTableFieldList = False Range("B8").Select With ActiveSheet.PivotTables("PivotTable1") .InGridDropZones = True .RowAxisLayout xlTabularRow End With Range("B4").Select Selection.Ungroup Range("D15").Select ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "" Range("C4").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("Area").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Range("D4").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("ORG NAME").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) Range("E4").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("CUSTOMER/END CUSTOMER"). _ Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _ False, False) Range("F4").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("Order").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Range("G4").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("Class").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Range("H4").Select ActiveSheet.PivotTables("PivotTable1").PivotFields("PID").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("H:H").EntireColumn.AutoFit ActiveSheet.PivotTables("PivotTable1").PivotSelect "'Final SSD'[All;Total]", _ xlDataAndLabel, True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With ActiveSheet.PivotTables("PivotTable1").PivotSelect _ "'Final SSD week'[All;Total]", xlDataAndLabel, True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 15773696 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range("D1").Select End Sub Sub Probalgatas() ' ' Probalgatas Macro ' Semmi, csak try ' ' Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select End Sub Sub Probalgatas_2() ' ' Probalgatas_2 Macro ' ' Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Range("A1:BM810").Select Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "BUD_SSCO!R1C1:R810C65", Version:=6).CreatePivotTable TableDestination:= _ "SCO_PIVOT!R3C1", TableName:="PivotTable2", DefaultVersion:=6 Sheets("SCO_PIVOT").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable2") .ColumnGrand = True .HasAutoFormat = True .DisplayErrorString = False .DisplayNullString = True .EnableDrilldown = True .ErrorString = "" .MergeLabels = False .NullString = "" .PageFieldOrder = 2 .PageFieldWrapCount = 0 .PreserveFormatting = True .RowGrand = True .SaveData = True .PrintTitles = False .RepeatItemsOnEachPrintedPage = True .TotalsAnnotation = False .CompactRowIndent = 1 .InGridDropZones = False .DisplayFieldCaptions = True .DisplayMemberPropertyTooltips = False .DisplayContextTooltips = True .ShowDrillIndicators = True .PrintDrillIndicators = False .AllowMultipleFilters = False .SortUsingCustomLists = True .FieldListSortAscending = False .ShowValuesRow = False .CalculatedMembersInFilters = False .RowAxisLayout xlCompactRow End With With ActiveSheet.PivotTables("PivotTable2").PivotCache .RefreshOnFileOpen = False .MissingItemsLimit = xlMissingItemsDefault End With ActiveSheet.PivotTables ("PivotTable2") ActiveWorkbook.ShowPivotTableFieldList = True With ActiveSheet.PivotTables("PivotTable2").PivotFields("Final SSD week") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("Final SSD") .Orientation = xlRowField .Position = 2 End With ActiveSheet.PivotTables("PivotTable2").PivotFields("Final SSD").AutoGroup With ActiveSheet.PivotTables("PivotTable2").PivotFields("Area") .Orientation = xlRowField .Position = 5 End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("ORG NAME") .Orientation = xlRowField .Position = 6 End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("CUSTOMER/END CUSTOMER" _ ) .Orientation = xlRowField .Position = 7 End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("Order") .Orientation = xlRowField .Position = 8 End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("Class") .Orientation = xlRowField .Position = 9 End With With ActiveSheet.PivotTables("PivotTable2").PivotFields("PID") .Orientation = xlRowField .Position = 10 End With ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ "PivotTable2").PivotFields("Net Open Qty"), "Sum of Net Open Qty", xlSum Range("B6").Select With ActiveSheet.PivotTables("PivotTable2") .InGridDropZones = True .RowAxisLayout xlTabularRow End With ActiveSheet.PivotTables("PivotTable2").PivotSelect "'2019'", xlDataAndLabel, _ True Range("F10").Select ActiveWorkbook.ShowPivotTableFieldList = False ActiveSheet.PivotTables("PivotTable2").TableStyle2 = "" Range("B4").Select Selection.Ungroup Range("C4").Select ActiveSheet.PivotTables("PivotTable2").PivotFields("Area").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("PivotTable2").PivotSelect "ORG NAME", xlButton, True ActiveSheet.PivotTables("PivotTable2").PivotFields("ORG NAME").Subtotals = _ Array(False, False, False, False, False, False, False, False, False, False, False, False) Range("E4").Select ActiveSheet.PivotTables("PivotTable2").PivotFields("CUSTOMER/END CUSTOMER"). _ Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _ False, False) Range("F4").Select ActiveSheet.PivotTables("PivotTable2").PivotFields("Order").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Range("G4").Select ActiveSheet.PivotTables("PivotTable2").PivotFields("Class").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Range("H4").Select ActiveSheet.PivotTables("PivotTable2").PivotFields("PID").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Columns("F:F").EntireColumn.AutoFit Columns("G:G").EntireColumn.AutoFit Columns("H:H").EntireColumn.AutoFit ActiveSheet.PivotTables("PivotTable2").PivotSelect "'Final SSD'[All;Total]", _ xlDataAndLabel, True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With ActiveSheet.PivotTables("PivotTable2").PivotSelect _ "'Final SSD week'[All;Total]", xlDataAndLabel, True With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 15773696 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range("D10").Select ActiveWorkbook.Save End Sub
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"BUD_SSCO!R1C1:R810C65", Version:=6).CreatePivotTable TableDestination:= _
"SCO_PIVOT!R3C1", TableName:="PivotTable2", DefaultVersion:=6 <- Ezzel bukik ki. 

Előre is köszi a segítséget.

Mutasd a teljes hozzászólást!

  • Szia Alexzjeah!

    Kérdésem: Alapból van Sheet3 füled?
    Mutasd a teljes hozzászólást!
  • Az elejére már kellene egy Sheets("Jó munkalap").Select utasítás. Szerintem azon a lapon frissítel, amelyiken nincs A pivot2 táblád, pont az előző miatt.
    Meg a Sheets.add is veszélyes csak így, mert ha újrafut, akkor már nem Pivot2 lesz, hanem Pivot3 a neve.
    Inkább új fájlt nyiss meg, és másolj oda.
    pl:  'átmásoljuk új lapra ée lementjük:
        wb = ActiveWorkbook.Path
        Sheets("Segéd").Select
        Sheets("Segéd").Copy
     
        wb = wb & "" & neva & " " & kezd - 1 & "_" & vege - 1 & ".xlsx"
        ActiveWorkbook.SaveAs wb
        ActiveWindow.Close
    Mutasd a teljes hozzászólást!
Tetszett amit olvastál? Szeretnél a jövőben is értesülni a hasonló érdekességekről?
abcd