Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot macro problems (Excel 2000)

    I created this macro to do a pivot table creation. Then I attempted to amend it as shown below to be applicable to handle various account databases I need to pivot, saving time re-creating the table, when I knew what I wanted. I checked my column headings very carefully yet I still get stuck at the >>>s with an Invalid Procedure call or argument problem. Yet I thought I was amending a macro that worked. Can you spot where I am off? The table gets up to that point-where I want to put in the data fields, when it stops. I also want to be sure the fields are xlsums, in a number format, and not count of's that I constantly get because all fields do not have data in each column. Thanks.



    Sub secondPivot()
    '
    'Second Pivot Macro derived
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
    Sheets("Data").UsedRange).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Account.")
    .Orientation = xlPageField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Source")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("JE Name")
    .Orientation = xlRowField
    .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Batch Name")
    .Orientation = xlRowField
    .Position = 2
    End With

    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Description")
    .Orientation = xlRowField
    .Position = 2
    End With

    >>>>>>>>>ActiveSheet.PivotTables("PivotTable1").Ad dDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Debits"), "Debits", xlSum

    ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Credit"), "Credits", xlSum
    Range("C4").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Debits")
    ' .Function = xlSum
    .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    ' End With
    Range("C5").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Credits")
    ' .Function = xlSum
    .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
    End With
    ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add "Net", _
    "=Debit+Credit", True
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("Net").Orientation = _
    xlDataField
    ' Range("C6").Select
    '' With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Batch Name")
    ' .Orientation = xlRowField
    ' .Position = 2
    End With
    Columns("A:E").Select
    Selection.ColumnWidth = 15
    Columns("B").Select
    Selection.ColumnWidth = 25
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot macro problems (Excel 2000)

    I don't know if this is your problem (I can't test too much of it, it is not XL97 compatible).

    I didn't think the "AddDataField" method existed until XL2002 VBA? Is this the problem?

    Steve

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

    Re: Pivot macro problems (Excel 2000)

    Just to confirm that AddDataField is listed under "What's new" in the online help for Excel 2002 VBA.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot macro problems (Excel 2000)

    Sorry, it is in Excel 2002. Just checked my About window. I checked very carefully the column headings so they would match the names in the code. Changing the xlCount to xlSum was done in the MacroRecorder as part of a With EndWith setup. COuld that be it? I thought I could amend the wizard steps. Perhaps I can't. TYIA

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

    Re: Pivot macro problems (Excel 2000)

    Try this:

    With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d(ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Debits"))
    .Caption = "Credit"
    .Function = xlSum
    End With
    With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d(ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Credits"))
    .Caption = "Debit"
    .Function = xlSum
    End With

    Also, in defining the formula for the calculated Net field, you must use the name of the original fields Debits and Credits, not Debit and Credit:

    ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add "Net", _
    "=Debits+Credits", True

Posting Permissions

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