Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Pivot Table VBA Code

  • 16-04-2009 2:32pm
    #1
    hallelujah
    Closed Accounts Posts: 584 ✭✭✭


    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


Comments

  • #2
    --amadeus--
    Closed Accounts Posts: 5,096 ✭✭✭--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.


Advertisement