Solved: Create pivot table help

[fwawergurl16]

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)
[vba]
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

[/vba]
All help much appreciated

[rory]

For your table by total, you want:
[VBA]Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim rngSource As Range
Dim wks As Worksheet

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:=Sheet1.Range("A1").CurrentRegion.Address)

'Add new worksheet
Set wks = Worksheets.Add
wks.Name = "PivotSheet"

'Create the pivot table from the cache
Set PT = PTCache.CreatePivotTable( _
TableDestination:=wks.Range("A1"), _
TableName:="BudgetPivot")

With PT
'Add fields
.AddFields RowFields:=Array("Product", "CopyLine", "Genre", "Media", "Duration")
'Add calculated items
.PivotFields("RM").Orientation = xlDataField
'.PivotFields("Count of RM").CalculatedItems.Add _
'"Q1", "=RM"
.TableRange1.EntireColumn.AutoFit
End With

End Sub
[/VBA]

[rory]

I forgot to mention, you should never give your modules the same name as one of your procedures.

[fwawergurl16]

Thank u SO much, Rory! Time after time u've 'saved my life'! Really thankful. Will remember that

Cheers!
Ruth

[fwawergurl16]

Oh btw, Rory. I noticed that my macro has to be in the same workbook as the data that I want to pivot table. Are there any ways to change this?

[rory]

Yes, you can put the macro in any workbook you like as long as you alter the code to specify which workbook you are working on, or ensure that the workbook is active at the time the code runs.

[fwawergurl16]

Thanks for all your help, Rory.