Snasui Excel Forum

Code: Select all

Sub pivot_table()
    Dim pt_cache As PivotCache
    Dim pt_table As PivotTable
    Set pt_cache = ActiveWorkbook.PivotCaches.Add( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("A").UsedRange)
    Worksheets("B").UsedRange.Clear 'Add this line
    Set pt_table = pt_cache.CreatePivotTable( _
        TableDestination:=Worksheets("B").Range("A1"), _
        TableName:="PivotTable1")
    With pt_table
        .PivotFields("Item").Orientation = xlRowField
        .PivotFields("Oty.").Orientation = xlDataField
        .PivotFields("Day").Orientation = xlColumnField
    End With
End Sub

Code: Select all

Sub pivot_table()
Dim pt_cache As PivotCache
Dim pt_table As PivotTable

Set pt_cache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Worksheets("A").Range("A1:I7000"))
Set pt_table = pt_cache.CreatePivotTable(TableDestination:=Worksheets("B").Range("A1"), TableName:="PivotTable1")
With pt_table
    .PivotFields("Item").Orientation = xlRowField
    .PivotFields("Oty.").Orientation = xlDataField
    .PivotFields("Day").Orientation = xlColumnField
    
End With

End Sub