Excel Forum

Code: Select all

Sub Pivot1()
' Pivot1 Macro

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "201609!R1C1:R8C4", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="201609!R1C8", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("201609").Select
    Cells(1, 8).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Material")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
End Sub

Code: Select all

Sub Pivot1()
    Dim pvCount As Integer
    Dim pvName As String
    Dim sh As Worksheet
    For Each sh In Worksheets
        pvCount = sh.PivotTables.Count
        pvName = "Pvt_" & pvCount + 1
        sh.Range("h1").CurrentRegion.Clear
        sh.Parent.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            sh.Range("a1").CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:=sh.Range("h1"), TableName:=pvName, DefaultVersion _
            :=xlPivotTableVersion14
        With sh.PivotTables(pvName).PivotFields("Material")
            .Orientation = xlRowField
            .Position = 1
        End With
        sh.PivotTables(pvName).AddDataField sh.PivotTables( _
            pvName).PivotFields("Quantity"), "Sum of Quantity", xlSum
    Next sh
End Sub