Sub BUYMATCD()
Dim paneru As Range
Dim TCD As Worksheet
Dim teburu As PivotTable
Set paneru = ActiveWorkbook.Sheets(1).UsedRange
Set TCD = Sheets.Add(After:=Sheets(Sheets.Count))
ActiveSheet.Name = "テーブル"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=paneru, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="テーブル!R1C1", TableName:="仕分け", DefaultVersion:=xlPivotTableVersion14
TCD.Range("A1").Select
Set teburu = ActiveSheet.PivotTables("仕分け")
With teburu.PivotFields("商品コード")
.Orientation = xlRowField
.Position = 1
End With
With teburu.PivotFields("サイズ")
.Orientation = xlRowField
.Position = 2
End With
With teburu
.AddDataField teburu.PivotFields("不変ID"), "個数", xlCount
.TableStyle2 = "PivotStyleMedium2"
.RowAxisLayout xlOutlineRow
.RepeatAllLabels xlRepeatLabels
End With
retsu = TCD.UsedRange.Rows.Count - 1
Range("A1:C" & retsu).Copy
Range("D1").PasteSpecial Paste:=xlPasteValues
Range("D1").PasteSpecial Paste:=xlPasteFormats
Range("D:F").AutoFilter
Range("D:F").AutoFilter Field:=1, Operator:=xlFilterNoFill
Columns("A:C").EntireColumn.Hidden = True
End Sub