PDA

View Full Version : Solved: Create pivot table help



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:

rory
08-15-2007, 02:26 AM
For your table by total, you want:
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

rory
08-15-2007, 02:42 AM
I forgot to mention, you should never give your modules the same name as one of your procedures.

fwawergurl16
08-15-2007, 08:15 PM
Thank u SO much, Rory! Time after time u've 'saved my life'! Really thankful. Will remember that :)

Cheers!
Ruth

fwawergurl16
08-29-2007, 08:43 PM
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
08-30-2007, 06:08 AM
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
09-03-2007, 06:48 PM
Thanks for all your help, Rory. :)