Pivot Tables With Vba

nattya · Mar 14th 2007

Hi,

I have the below code which will create a pivot table. Now suppose my data gets updated and i want a new pivot table but in different sheet it gives me error with table destination (may be because the name is similar).

Also will the code be able to identify the source data.

Code

Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R11637C6").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable3", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:=Array("P", _
        "CP", "  NAME", " NO")
    ActiveSheet.PivotTables("z").PivotFields("ages"). _
        Orientation = xlDataField
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields("CP").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    Range("C4").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields(" NAME").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveWorkbook.ShowPivotTableFieldList = False

Display More

Thanks,
Nattya

Robert B · Mar 15th 2007

Re: Pivot Tables With Vba

Hi

you might find it more flexible to use VBA in the form below, rather than a recorded macro. This code will create a Pivot Table based on the data in the ActiveSheet, given that you identify the sources of the Rows,Column, Pages and Data.

Code

Dim myPivot As PivotTable,myField As PivotField, myRange As Range


Selection.CurrentRegion.Select
Set myRange = Selection
    Names.Add Name:="Database", RefersTo:=myRange
Set myPivot = ActiveSheet.PivotTableWizard
    Set myField = myPivot.PivotFields("Your Field Name")
    myField.Orientation = xlDataField
    myField.Function = 'NumberFormat
    myField.Name = "Your Heading"
    
Set myField = myPivot.PivotFields("Your Field Name")
    myField.Orientation = xlRowField
    myField.Name = "Your Heading"
    
Set myField = myPivot.PivotFields("Your Field Name")
    myField.Orientation = xlColumnField
    myField.Name = "Your Heading"
    myField.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
       
Set myField = myPivot.PivotFields("Your Field Name")    
    myField.Orientation = xlPageField
    RowGrand = False
    ColumnGrand = False

Display More

Each time this is run a new Pivot will be created in a new Sheet

If you were to post your workbook containing the data I could fill in the headings etc.

HTH

Robert

Pivot Tables With Vba

Participate now!

Share