¨D°Ý¡GVBA ¦Û°Ê²£¥Í¼Ï¯Ã¤ÀªRªí
- ©«¤l
- 401
- ¥DÃD
- 57
- ºëµØ
- 0
- ¿n¤À
- 484
- ÂI¦W
- 20
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-4-26
|
¨D°Ý¡GVBA ¦Û°Ê²£¥Í¼Ï¯Ã¤ÀªRªí
§Ú¥Î¿ý»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 |
|
|
|
|
|
|
- ©«¤l
- 401
- ¥DÃD
- 57
- ºëµØ
- 0
- ¿n¤À
- 484
- ÂI¦W
- 20
- §@·~¨t²Î
- win7
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-10-30
- ³Ì«áµn¿ý
- 2024-4-26
|
§Ú¥Î¿ý»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 |
|
|
|
|
|
|