[RESOLVED] VBA: Help needed to create pivot table

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) :confused: . 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)

Code:

---

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

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

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

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