Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Pivot Table Macro (Excel 2002)

    Hi,
    I've recorded the macro below and was wondering how I could change the pivot table name to NOT be numeric (in the macro it's "PivotTable20) because I've obviously repeatedly recorded and deleted a pivot table 20 times in this worksheet.... likewise, how would I change the database range to be each time the macro runs as the database size (# of rows) changes each time the macro is run... in other words, the data will not always be "data!R2C1:R270C10". I suppose I could just have it read 65,536 lines and hide the blanks?
    As always, thanks for the help!
    Lana

    Sub CreatePivotTable()
    Range("A2").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    "data!R2C1:R270C10").CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable20", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable20").ColumnGran d = False
    ActiveSheet.PivotTables("PivotTable20").PivotField s("Unit ID").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable20").AddFields RowFields:=Array("Unit ID", _
    "Invoice #"), PageFields:="Action"
    ActiveSheet.PivotTables("PivotTable20").PivotField s("Amount").Orientation = _
    xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    Sheets("Sheet2").Select
    ActiveSheet.PivotTables("PivotTable20").PivotField s("Action").CurrentPage = _
    "Fix"
    ActiveSheet.PivotTables("PivotTable20").PivotSelec t "'Row Grand Total'", _
    xlDataAndLabel, True
    Selection.Style = "Comma"
    Columns("C:C").EntireColumn.AutoFit
    ActiveWorkbook.ShowPivotTableFieldList = False
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Macro (Excel 2002)

    You can specify another name for the pivot table, as long as you use the name consistently. Or you can omit specifying the name, and refer to PivotTables(1) in the rest of the code, assuming that you have only one pivot table on the sheet.

    Instead of the fixed range, you can use this:

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Worksheets("Data").Range("A2").CurrentRegion).Crea tePivotTable ...

    CurrentRegion tells Excel to use the entire contiguous block of cells that contains A2.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Macro (Excel 2002)

    It doesn't like my first line of code... I'm assuming I've named the Pivot Table incorrectly?

    Sub CreatePivotTable()

    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Worksheets("Data").Range("A2").CurrentRegion).Crea tePivotTable TableDestination:="", TableName:= _
    (1), DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables(1).ColumnGrand = False
    ActiveSheet.PivotTables(1).PivotFields("Unit ID").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables(1).AddFields RowFields:=Array("Unit ID", _
    "Invoice #"), PageFields:="Action"
    ActiveSheet.PivotTables(1).PivotFields("Amount").O rientation = _
    xlDataField
    ActiveWorkbook.ShowPivotTableFieldList = True
    Sheets("Sheet2").Select
    ActiveSheet.PivotTables(1).PivotFields("Action").C urrentPage = _
    "Fix"
    ActiveSheet.PivotTables(1).PivotSelect "'Row Grand Total'", _
    xlDataAndLabel, True
    Selection.Style = "Comma"
    Columns("C:C").EntireColumn.AutoFit
    ActiveWorkbook.ShowPivotTableFieldList = False
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Macro (Excel 2002)

    You must either provide a real name for the pivot table, for example
    <code>
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Worksheets("Data").Range("A2").CurrentRegion).Crea tePivotTable TableDestination:="", TableName:= _
    "MyGorgeousPivotTable", DefaultVersion:=xlPivotTableVersion10
    </code>
    or omit the TableName argument altogether
    <code>
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Worksheets("Data").Range("A2").CurrentRegion).Crea tePivotTable TableDestination:="", _
    DefaultVersion:=xlPivotTableVersion10
    </code>
    In both cases, you can refer to PivotTables(1) further on. In this expression, the 1 is not the name of the pivot table, but its index number - it means "the first pivot table on the sheet". Since there is only one, there can be no confusion which one that is.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Macro (Excel 2002)

    Darn, now I get the following error message:
    Run time error 1004
    The PivotTable field name is not valid.... etc.

    It then highlights the first section of the code - see below
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Worksheets("Data").Range("A2").CurrentRegion).Crea tePivotTable TableDestination:="", TableName:= _
    "MyGorgeousPivotTable", DefaultVersion:=xlPivotTableVersion10

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Macro (Excel 2002)

    Which range contains the field names (column names) of your data?

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Macro (Excel 2002)

    Range A2:J2 contain the field headings (for example "Month", "Amount", "Invoice #")

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Macro (Excel 2002)

    OK. Would it be acceptable to insert a row at row 2, so that there is an empty row between the cells in row 1 and the column headers that have been shifted down to row 3? If so, change Range("A2") in the code to Range("A3") and it should work OK.
    Post back if inserting a row is not possible for design reasons.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Macro (Excel 2002)

    Ok, this makes sense, as I did have notes written in A1, which was probably causing a problem with the whole CurrentRegion thing... any it worked, however now I'm having a problem with the destination of where the pivot table is to reside... I tried to rename where it goes (see below) and it doesn't like it.
    ActiveSheet.PivotTableWizard TableDestination:= _
    Worksheets.("Sheet3").Range("A1")

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Macro (Excel 2002)

    I figured it out... I had a period after the word Worksheet... oops. Thanks for all your help Hans!
    Lana

Posting Permissions

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