Pivot Table VBA Code

hallelujah

Hi all,

Just wondering if anyone has experience of Pivot Tables in MS Excel. I have a sheet in Excel with a large number of rows. I have another sheet with a pivot table representing this data. I created this Pivot table using a wizard. I want to be able to create the Pivot Table using a macro - I hit a button and the Pivot table is automatically recreated.

Before I start writing my own VBA code, do any of you know if it's possible to view the code that created the Pivot table in the first place? Does such code exist or is it hidden from the user?

Thanks,
Jeff

--amadeus--

Do you have experience coding in VBA?

The "code" that creates the pivot table is part of the application functionality so you can't get at it but you can record yourself creating a pivot table using the Record Macro function and get at the VBA code that way. You'll end up with:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        [I]OriginalDataAddress[/I]).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array([I]Field[/I], _
        [I]Field5[/I]), ColumnFields:=[I]Field1[/I], PageFields:=[I]Field2[/I]
    ActiveSheet.PivotTables("PivotTable1").PivotFields([I]Field3[/I]).Orientation = _
        xlDataField

Automating it will be straightforward enough if teh layout is static, you'll just need to capture the address of the range that holds the source data, assuming it won't always be teh same size.