fwawergurl16
08-14-2007, 11:43 PM
:help Im an intern with a media company in Malaysia & I am asked to do automation work in Excel (I've no prior knowledge of VBA). One of the tasks is to automate the creation of pivot tables from a set of data. I have some codes which I got from a book, & I've tried to experiment & play around with it to suit my task, but it still doesn't give me the desired result.
Could anyone pls help me out? (no one in my company knows VBA :( ) I've included the excel sheet for the raw data & the pivot tables (2) done manually too.
Below is the code that I've played with...(all 'should be ok' except the "Total" part)
Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim rngSource As Range
Application.ScreenUpdating = False
'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
'Add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
'Create the pivot table from the cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="BudgetPivot")
With PT
'Add fields
.PivotFields("Product").Orientation = x1RowField
.PivotFields("CopyLine").Orientation = x1RowField
.PivotFields("Genre").Orientation = x1RowField
.PivotFields("Media").Orientation = x1RowField
.PivotFields("Duration").Orientation = x1RowField
'Add calculated items
.PivotFields("RM").Orientation = x1ColumnField
'.PivotFields("Count of RM").CalculatedItems.Add _
'"Q1", "=RM"
End With
End Sub
All help much appreciated :bow:
Could anyone pls help me out? (no one in my company knows VBA :( ) I've included the excel sheet for the raw data & the pivot tables (2) done manually too.
Below is the code that I've played with...(all 'should be ok' except the "Total" part)
Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim rngSource As Range
Application.ScreenUpdating = False
'Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)
'Add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
'Create the pivot table from the cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet").Range("A1"), _
TableName:="BudgetPivot")
With PT
'Add fields
.PivotFields("Product").Orientation = x1RowField
.PivotFields("CopyLine").Orientation = x1RowField
.PivotFields("Genre").Orientation = x1RowField
.PivotFields("Media").Orientation = x1RowField
.PivotFields("Duration").Orientation = x1RowField
'Add calculated items
.PivotFields("RM").Orientation = x1ColumnField
'.PivotFields("Count of RM").CalculatedItems.Add _
'"Q1", "=RM"
End With
End Sub
All help much appreciated :bow: