I'm trying to find a way to create a pivot table from a worksheet (i.e. "Monthly Data"), but i'm having a little trouble with code.
[VBA]Sub Pivot()
Sheets.Add
ActiveSheet.Name = "Monthly Data"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Master!R1C1:R5038C37", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Sheet26!R3C1", TableName:="PivotTable4", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Sheet26").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Created By Name")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Status"), "Count of Status", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Count of Status")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Created"), "Count of Created", xlCount
Range("C4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Status").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("D4").Select
ActiveSheet.PivotTables("PivotTable4").RowGrand = False
Range("C4").Select
ActiveSheet.PivotTables("PivotTable4").PivotFields("Status").PivotItems( _
"Opened").Position = 1
End Sub
[/VBA]