none
Arrays, VBA, and pivot tables (and a couple of additional questions RRS feed

  • Question

  • Hello all-

    I have some beginner code that creates a pivot table from an existing workbook that works in its basic form-

    I am trying to do four things-

    1) Create an array so the summed column fields (Sales, Stores)  are right next to each other vs stacked on top of each other

    2) Not start in cell A1 so that I can add a title in that cell (and how do I do that)

    3) Change the design type -i.e. PivotStyleMedium9

    Any help much appreciated-

    4) rename the sheet of the pivot table-

    It doesn't seem to be taking-

    Thank you

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ActiveWorkbook.Sheets("Shoes").Select
    Range("A1").Select
    Set objTable = Sheet2.PivotTableWizard
    objTable.Name = "Shoes sales"



    Set objField = objTable.PivotFields("REGION")
    objField.Orientation = xlRowField

    Set objField = objTable.PivotFields("PRODUCT")
    objField.Orientation = xlRowField






    Set objField = objTable.PivotFields("SALES")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"
    objField.Caption = "Total Sales"




    Set objField = objTable.PivotFields("STORES")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "####"
    objField.Caption = "Total Stores"



    Set objField = objTable.PivotFields("Subsidiary")
    objField.Orientation = xlPageField
    objField.Caption = "BLAH BLAH BLAH"


    End Sub

    Thursday, October 29, 2015 8:36 PM

Answers

  • Hi,

    Too many questions in a thread, I'd suggest asking separate questions in new forum threads. Thus, forum readers may recognize questions and answers easily.

    >> Create an array so the summed column fields (Sales, Stores)  are right next to each other vs stacked on top of each other

    What’s it like? Please provide the sample. I suggest that you could do it through the UI and record the macro, then check the macro code directly.

    >> Not start in cell A1 so that I can add a title in that cell (and how do I do that)

    You could create a PivotTable by using PivotCache.CreatePivotTable method.

    For example:

    Sub sbCreatePivot()
    
    'Declaration
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    
    'Adding new worksheet
    Set ws = Worksheets.Add
    
    'Creating Pivot cache
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R10C3")
    
    'Creating Pivot table
    Set pt = pc.CreatePivotTable(ws.Range("B3"))
    
    'Setting Fields
    With pt
        'set row field
        With .PivotFields("Department")
        .Orientation = xlRowField
        .Position = 1
        End With
        
        'set column field
        With .PivotFields("Region")
        .Orientation = xlColumnField
        .Position = 1
        End With
        
        'set data field
        .AddDataField .PivotFields("Profit"), "Sum of Profit", xlSum
    End With
    
    End Sub

    >> 3) Change the design type -i.e. PivotStyleMedium9

    PivotTables("Shoes sales").TableStyle2 = " PivotStyleMedium9"

    >> rename the sheet of the pivot table-

    PivotTables("Shoes sales").Name=”XXX”

    Regards

    Starain


    Friday, October 30, 2015 8:43 AM
    Moderator

All replies

  • Hi,

    Too many questions in a thread, I'd suggest asking separate questions in new forum threads. Thus, forum readers may recognize questions and answers easily.

    >> Create an array so the summed column fields (Sales, Stores)  are right next to each other vs stacked on top of each other

    What’s it like? Please provide the sample. I suggest that you could do it through the UI and record the macro, then check the macro code directly.

    >> Not start in cell A1 so that I can add a title in that cell (and how do I do that)

    You could create a PivotTable by using PivotCache.CreatePivotTable method.

    For example:

    Sub sbCreatePivot()
    
    'Declaration
    Dim ws As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    
    'Adding new worksheet
    Set ws = Worksheets.Add
    
    'Creating Pivot cache
    Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Sheet1!R1C1:R10C3")
    
    'Creating Pivot table
    Set pt = pc.CreatePivotTable(ws.Range("B3"))
    
    'Setting Fields
    With pt
        'set row field
        With .PivotFields("Department")
        .Orientation = xlRowField
        .Position = 1
        End With
        
        'set column field
        With .PivotFields("Region")
        .Orientation = xlColumnField
        .Position = 1
        End With
        
        'set data field
        .AddDataField .PivotFields("Profit"), "Sum of Profit", xlSum
    End With
    
    End Sub

    >> 3) Change the design type -i.e. PivotStyleMedium9

    PivotTables("Shoes sales").TableStyle2 = " PivotStyleMedium9"

    >> rename the sheet of the pivot table-

    PivotTables("Shoes sales").Name=”XXX”

    Regards

    Starain


    Friday, October 30, 2015 8:43 AM
    Moderator
  • Many Thanks Starain! 

    I will take a look at this!

    Lawrence

    Monday, November 2, 2015 9:49 PM