¨D°Ý¡GVBA ¦Û°Ê²£¥Í¼Ï¯Ã¤ÀªRªí

198188

§Ú¥Î¿ý»s¤è¦¡¼g¤F¤@­ÓVBA ¬O¦bªþ¥óùثإߨâ­Ó¼Ï¯Ã¤ÀªRªí¡C¤£¹L¹B¦æªº®É­Ô¦b¤U­±³o­Ó¦ì¸m¥d¦í¤F¡C   
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "sheet1!R1C1:R1048576C12", Version:=8).CreatePivotTable TableDestination:= _
        "sheet1!R1C26", TableName:="PivotTable21", DefaultVersion:=8
½ÐÀ°§Ú¬Ý¬Ý¬O­þùØ¥X²{°ÝÃD¡C

¥t¥~¯à¤£¯àÀ°¦£§ï¨}¤@¤U¡A
1) ÄæZ -AAªº¼Ï¯Ã¤ÀªRªí
¸ê®Æ¨Ó·½±qA1  - L1  :  A (ÄæB³Ì«á¤@¦æ¡^ -  L(ÄæB³Ì«á¤@¦æ¡^
Äæ¦ì²M³æ¡G
¦C ¡G SECTION & LENGTH
­È ¡G ¥[Á`QTY

2) ÄæAC-ADªº¼Ï¯Ã¤ÀªRªí
¸ê®Æ¨Ó·½±qN1  - V1  :  N (ÄæP³Ì«á¤@¦æ¡^ -  V(ÄæP³Ì«á¤@¦æ¡^
Äæ¦ì²M³æ¡G
¦C ¡G SECTION & LENGTH
­È ¡G ¥[Á`Q'TY

Sub Macro2()
  Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "sheet1!R1C1:R1048576C12", Version:=8).CreatePivotTable TableDestination:= _
        "sheet1!R1C26", TableName:="PivotTable21", DefaultVersion:=8
    Sheets("sheet1)").Select
    Cells(1, 26).Select
    With ActiveSheet.PivotTables("PivotTable21")
        .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("PivotTable21").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable21").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable21").PivotFields("SECTION")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable21").PivotFields("LENGTH")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable21").AddDataField ActiveSheet.PivotTables( _
        "PivotTable21").PivotFields("QTY"), "Count of QTY", xlCount
    With ActiveSheet.PivotTables("PivotTable21").PivotFields("Count of QTY")
        .Caption = "Sum of QTY"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

198188

§Ú¥Î¿ý»s¤è¦¡¼g¤F¤@­ÓVBA ¬O¦bªþ¥óùثإߨâ­Ó¼Ï¯Ã¤ÀªRªí¡C¤£¹L¹B¦æªº®É­Ô¦b¤U­±³o­Ó¦ì¸m¥d¦í¤F¡C   
Activ ...
198188 µoªí©ó 2024-2-21 15:30

    ¦³¨S¦³¤j¤j¯àÀ°¦£°µ³o­Ó¡H

Andy2483

¦^´_ 1# 198188


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«¾Ç²ß¨ì«Ü¦hª¾ÃÑ,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò
°õ¦æµ²ªG:

¤U¸ü (298.7 KB)

2024-2-22 16:39

Option Explicit
Sub TEST_P1()
[Z:AD].Delete
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range([L1], [A65536].End(3)), Version:=4) _
   .CreatePivotTable TableDestination:=[Z1], TableName:="PivotTable21", DefaultVersion:=4
With ActiveSheet.PivotTables("PivotTable21").PivotFields("SECTION")
   .Orientation = xlRowField: .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable21").PivotFields("LENGTH")
   .Orientation = xlRowField: .Position = 2
End With
ActiveSheet.PivotTables("PivotTable21").AddDataField ActiveSheet.PivotTables("PivotTable21").PivotFields("QTY"), "Sum of QTY", xlSum
'===============================================================================================
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Range([V2], [O65536].End(3)(1, 0)), Version:=4) _
   .CreatePivotTable TableDestination:=[AC1], TableName:="PivotTable22", DefaultVersion:=4
With ActiveSheet.PivotTables("PivotTable22").PivotFields("NO.3")
   .Orientation = xlRowField: .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable22").PivotFields("(M.)")
   .Orientation = xlRowField: .Position = 2
End With
ActiveSheet.PivotTables("PivotTable22").AddDataField ActiveSheet.PivotTables("PivotTable22").PivotFields("(Pcs.)"), "Sum of QTY", xlSum
End Sub