Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Creating a pivot table using a macro

This is a discussion on Creating a pivot table using a macro within the Excel Questions forums, part of the Question Forums category; ...

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Location
    UK
    Posts
    99

    Default Creating a pivot table using a macro


  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,632

    Default Re: Creating a pivot table using a macro

    Turn Macro recorder on.
    Create a pivot table.
    Turn Macro recorder off.
    "Fair Winds and Following Seas"

  3. #3
    Board Regular
    Join Date
    Dec 2003
    Location
    UK
    Posts
    99

    Default Re: Creating a pivot table using a macro

    For some reason the text has appeared on my first post....

    I have recorded a macro while creating a pivot table but the range that the original table was created from will change from week to week. I need to write the macro to select the current range on the sheet the table is being created from!

    Can anyone tell me how to do this????



    Code:
        ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
            "'Prior To Pivot Table'!R2C1:R1194C3", TableDestination:="", TableName:= _
            "PivotTable2"
        ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Proj_only" _
            , "Data")
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("GL WIP")
            .Orientation = xlDataField
            .Name = "Sum of GL WIP"
            .Position = 1
            .Function = xlSum
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Con Reg WIP")
            .Orientation = xlDataField
            .Name = "Sum of Con Reg WIP"
            .Function = xlSum
        End With
    End Sub

  4. #4
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,632

    Default Re: Creating a pivot table using a macro

    I'm not an expert but changing the first part to:


    x = Selection.Address

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=x ...

    worked for me.
    "Fair Winds and Following Seas"

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    93

    Default Re: Creating a pivot table using a macro

    I think CurrentRegion will do it.

    'Make Pivot Tables
    Selection.CurrentRegion.Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    Selection.CurrentRegion, TableDestination:="", TableName:= _
    "PivotTable1"

  6. #6
    Board Regular
    Join Date
    Dec 2003
    Location
    UK
    Posts
    99

    Default Re: Creating a pivot table using a macro

    Thanks for the help...

    I altered fairwinds code slightly...

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "'Prior To Pivot Table'!" & x,
    As I was getting an error come up when the macro was trying to add the data!!

    duplinguy - Thanks for your help also may use this in the future!

  7. #7
    Board Regular
    Join Date
    Jun 2008
    Posts
    392

    Default Re: Creating a pivot table using a macro

    Hi Guys I am having this same issue but my code is a little differenct to start with and the suggested work arrounds give me compile errors?

    here is my code any help would be great

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    "'Applicant Volume Report'!R1C1:R848C8").CreatePivotTable TableDestination:= _
    "", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Vacancy Name", _
    ColumnFields:="Application Stage"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Application Stage"). _
    Orientation = xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False

  8. #8
    Board Regular
    Join Date
    Jun 2008
    Posts
    392

    Default Re: Creating a pivot table using a macro

    Bit cheeky I know,

    But does any one have an idea on this one yet?

  9. #9
    New Member
    Join Date
    Jun 2008
    Posts
    7

    Default Re: Creating a pivot table using a macro

    Sorry, I don't have time to get into detail but this is code I use over and over again (modified) to create pivot tables / charts with VBA.

    hope it helps...

    D

    Code:
     
    Sub MakeTable()
    'declare the row, column, page and data field variables
    Dim Pt As PivotTable
    Dim PtCache As PivotCache
    Dim pageField1 As String
    Dim pageField2 As String
    Dim pageField3 As String
    Dim rowField1 As String
    Dim rowField2 As String
    Dim colField As String
    Dim dataField As String
    'delete pivot sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot Sheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    '
    Set s = Sheets("Data")
    With Worksheets.Add
        .Name = "Pivot Sheet"
    End With
        
        'Pass variable names to a String variable
        pageField1 = s.Cells(1, 2).Value  'page variable "Dispo code"
        rowField1 = s.Cells(1, 3).Value  'row field "Op No"
          
        colField1 = s.Cells(1, 10).Value  'colums field "Defect"
        dataField = s.Cells(1, 11).Value  'data to summarize "Tons"
    'Name the list range
    Worksheets("Data").Activate
    ActiveSheet.Range("a1").Select
    ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "Items"
    'create pivot cache
    Set PtCache = ActiveWorkbook.PivotCaches.Add( _
        SourceType:=xlDatabase, _
        SourceData:=s.Range("Items")) 'entire contents of sheet
        
    'create pivot table from cache
    Set Pt = PtCache.CreatePivotTable( _
        TableDestination:=Sheets("Pivot Sheet").Range("A3"), _
        TableName:="QualCodeTable")
        
    'add fields
    With Pt
        .PivotFields(rowField1).Orientation = xlRowField   'Op No
        .PivotFields(pageField1).Orientation = xlPageField  'Dispo code
        .PivotFields(colField1).Orientation = xlColumnField  'Defect
        .PivotFields(dataField).Orientation = xlDataField  'sum of Tons
    End With
    Worksheets("Pivot Sheet").Columns("A:DD").AutoFit
    Application.ScreenUpdating = True
    'create pivot chart
    'first delete chart if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot Chart").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    '
        
        'add chart
        Sheets("Pivot Sheet").Activate
        Sheets("Pivot Sheet").Range("B6").Select  'selects a cell in the pivot table
        Charts.Add
        ActiveChart.SetSourceData Source:=Sheets("Pivot Sheet").Range("E6")  'selects a cell on the pivot sheet as the range
        ActiveChart.Location xlLocationAsNewSheet, "Pivot Chart"  'creates a new sheet named "Pivot Chart"
        'ActiveChart.ChartArea.Select
        ActiveChart.ChartType = xlColumnStacked
        
        
    End Sub

  10. #10
    Board Regular
    Join Date
    Jun 2008
    Posts
    392

    Default Re: Creating a pivot table using a macro

    Thank you very much for your help but I am getting a compile error at this point

    'Name the list range
    Worksheets("Data").Activate
    ActiveSheet.Range("a1").Select
    ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "Items"
    'create pivot cache

    Do I need to change anything?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com