[RESOLVED] VBA: Help needed to create pivot table

[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 (RM)" 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:="ActChartPivot") 
     
    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!

- fwawergurl16

[westconn1]

try to record a macro of creating the pivot table manually, you can then modify the code to suit your application

if you have problems post the code here with details of the problem

[fwawergurl16]

Thank you for helping, westconn1 I had my problem solved now. Thank u once again!!

[RobDog888]

Tutorial on Pivot Tables: http://vbforums.com/showthread.php?t=451725