+ Reply to Thread
Results 1 to 2 of 2

Run time error '5': Invalid procedure call or arguement (excel 2007)

  1. #1
    Registered User
    Join Date
    03-05-2012
    Location
    Charlotte, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Run time error '5': Invalid procedure call or arguement (excel 2007)

    I am trying to create a macro that will automatically create a pivot table and chart for the user. I get the runtime error and excel will bring up the MS VBA editor with this section of code highlighted

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "NEW_ORDERS!R2C20:R103C21", Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion10

    I would suspect this has something to do with me using Excel 2007 and saving the file as a xls instead of an xlsm. However I have tried using both file formats.

    Any help you can provide would be greatly appreciated. (Below is the entire section of code)

    Thanks for the help



    Sub NEWORDER_DEMAND()
    '
    ' NEWORDER_DEMAND Macro
    ' CREATES PIVOT CHART SHOWING THE DAILY DEMAND REQUIRED BY ALL OF THE NEW ORDERS JUST ENTERED
    '

    '
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "NEW_ORDERS!R2C20:R103C21", Version:=xlPivotTableVersion10).CreatePivotTable _
    TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion10
    Sheets("Sheet1").Select
    Cells(1, 1).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$G$14")
    ActiveChart.ChartType = xlColumnClustered
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("ORDER DUE DATE")
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("EST TOTAL TIME TO PROCESS JOB hh:mm:ss"), _
    "Count of EST TOTAL TIME TO PROCESS JOB hh:mm:ss", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "Count of EST TOTAL TIME TO PROCESS JOB hh:mm:ss")
    .Caption = "Sum of EST TOTAL TIME TO PROCESS JOB hh:mm:ss"
    .Function = xlSum
    .NumberFormat = "[h]:mm:ss"
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("ORDER DUE DATE")
    .PivotItems("").Visible = False
    .PivotItems("12:00:00 AM").Visible = False
    End With
    Range("A23").Select
    ActiveCell.FormulaR1C1 = "TOTAL DEMAND OF NEW ORDERS AS OF"
    Range("A24").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("A24").Select
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
    Range("A23:A24").Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range("D28").Select
    End Sub

  2. #2
    Registered User
    Join Date
    03-05-2012
    Location
    Charlotte, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Run time error '5': Invalid procedure call or arguement (excel 2007)

    The macro is trying to use SHEET1. If this has already been used that is what is causing the runtime error. How do I get the macro to increment to the next available sheet?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1