none
Runtime Error 5 running Microsoft Excel Macro

    Question

  • Hello Everyone. 

    My very first post so I am missing here something please forgive me!!

    I have been running this Macro for Microsoft Excel. I have been using this Macro I havent design this. 

    Now whenever I run this macro this giving me an error with Invalid Procedure Call or Argument = Run time Error = 5

    I have tried to debug the it point me to below line.

    '

    ' Pivot4 Macro

    '

     

    '

        Sheets("Summary").Select

        Sheets("Sheet1").Visible = True

        Range("A2:AR231").Select

        Sheets.Add

        ActiveWorkbook.Worksheets("MEGs & Targets").PivotTables("PivotTable6"). _

            PivotCache.CreatePivotTable TableDestination:="Sheet6!A3", TableName:= _

            "PivotTable8", DefaultVersion:=xlPivotTableVersion10   --- This is the point it actually STOP working. 

     

    Any help greatly appreciated. 

    Thanks 

     

    • Moved by Kee Poppy Monday, January 16, 2012 6:22 AM (From:Visual Basic Language)
    Saturday, January 14, 2012 10:51 PM

Answers

  • Hi,

    There are really many reasons causes the procedure crash.

    First you should be aware of how a PivotTable been created in background. If I want to create the first PivotTable for a workbook, Excel What it does first is create a PivotCache based on the data I specified, the next step is creating a PivotTable based on the PivotCache Excel just created in background at the place I specified.

    Thus next time you want to create an another PivotTable based on the same data, what Excel actually does is creating a new PivotTable  based on the PivotCache instead of the original data.

    So if your workbook doesn't contain a pt named "PivotTable6", or if the workbook does contain a pt named "PivotTable6" but the database of pt isn't the database you want for the new one, or if the workbook has already created pt named "PivotTable8", or if..your code would crash.

    Actually, we can't directly use the code recorded by macro under most situations, we would have do some modifications or optimizations.

    I would help if you could tell us what are trying to do.

    I look forward to hearing of you.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 16, 2012 8:28 AM
    Moderator
  • I don't think you're going to have a 'PivotTable6' and a 'PivotTable8' together; at least, not in the same Sub.

     

    Assume your data is stored on the 'C2_UnionQuery'; you build youtr PivotTable on the 'Summary-Sheet'.  Assume your PivotTable Fields are named 'Source', 'RVP', 'Director', etc:

     

        'Delete the sheet "Summary-Sheet" if it exist
        Application.DisplayAlerts = False
        On Error Resume Next
        ThisWorkbook.Worksheets("Summary-Sheet").Delete
        On Error GoTo 0
        Application.DisplayAlerts = True

        'Add a worksheet with the name "Summary-Sheet"
        Set Basebook = ThisWorkbook
        Set Newsh = Basebook.Worksheets.Add
        Newsh.Name = "Summary-Sheet"

       
        Sheets("C2_UnionQuery").Select
        Range("A1").Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets("C2_UnionQuery").Range("A1").CurrentRegion).CreatePivotTable _
        TableDestination:=Sheets("Summary-Sheet").Range("A3"), TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
       
    '****************************************
        Sheets("Summary-Sheet").Select
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Source")
            .Orientation = xlRowField
            .Position = 1
        End With
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("RVP")
            .Orientation = xlRowField
            .Position = 2
        End With
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Director")
            .Orientation = xlRowField
            .Position = 3
        End With
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("SalesRep")
            .Orientation = xlRowField
            .Position = 4
        End With

     

    Does that help???

    Tuesday, January 17, 2012 3:41 AM

All replies

  • I don't know that this is the best forum, but looking I couldn't find one for VBA questions.

    It looks like it's trying to create PivotTable8, which may be in another sheet, leading to a name conflict.  Have you checked to make sure another sheet is not using this table name? Have you checked to make sure ther is already a Sheet6 to add this to?


    Lines to Code by: Hardware can only do what it's told. That's why there are programmers. ** Just because you don't know how, doesn't mean it can't be done. ** When something doesn't work, it's usually a programming error. ** Just because you did something last time, is not a good enough reason to do it this time. ** If the user can do it, at some point they will. When they do, it's the programmer's fault. ** Keep your code modular. Someone else will have to read it at some point. ** Add comments. In six months, you won't know why you wrote the code like that.
    Saturday, January 14, 2012 11:58 PM
  • I don't know that this is the best forum, but looking I couldn't find one for VBA questions.

    If golfin86's post doesn't answer your question, I would suggest the Excel for Developers forum.
    Sunday, January 15, 2012 12:12 AM
  • Hi gujjuboy,

    We have Excel for Developers forum for discussions and questions involving Microsoft excel. I will move this thread there in case of you need.

    Have a nice day,


    Kee Poppy [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 16, 2012 6:22 AM
  • I don't know that this is the best forum, but looking I couldn't find one for VBA questions.

    Could try this forum which is specifically Visual Basic for Applications.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads


    Regards, OssieMac
    Monday, January 16, 2012 7:08 AM
  • Hi,

    There are really many reasons causes the procedure crash.

    First you should be aware of how a PivotTable been created in background. If I want to create the first PivotTable for a workbook, Excel What it does first is create a PivotCache based on the data I specified, the next step is creating a PivotTable based on the PivotCache Excel just created in background at the place I specified.

    Thus next time you want to create an another PivotTable based on the same data, what Excel actually does is creating a new PivotTable  based on the PivotCache instead of the original data.

    So if your workbook doesn't contain a pt named "PivotTable6", or if the workbook does contain a pt named "PivotTable6" but the database of pt isn't the database you want for the new one, or if the workbook has already created pt named "PivotTable8", or if..your code would crash.

    Actually, we can't directly use the code recorded by macro under most situations, we would have do some modifications or optimizations.

    I would help if you could tell us what are trying to do.

    I look forward to hearing of you.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 16, 2012 8:28 AM
    Moderator
  • I don't think you're going to have a 'PivotTable6' and a 'PivotTable8' together; at least, not in the same Sub.

     

    Assume your data is stored on the 'C2_UnionQuery'; you build youtr PivotTable on the 'Summary-Sheet'.  Assume your PivotTable Fields are named 'Source', 'RVP', 'Director', etc:

     

        'Delete the sheet "Summary-Sheet" if it exist
        Application.DisplayAlerts = False
        On Error Resume Next
        ThisWorkbook.Worksheets("Summary-Sheet").Delete
        On Error GoTo 0
        Application.DisplayAlerts = True

        'Add a worksheet with the name "Summary-Sheet"
        Set Basebook = ThisWorkbook
        Set Newsh = Basebook.Worksheets.Add
        Newsh.Name = "Summary-Sheet"

       
        Sheets("C2_UnionQuery").Select
        Range("A1").Select
        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets("C2_UnionQuery").Range("A1").CurrentRegion).CreatePivotTable _
        TableDestination:=Sheets("Summary-Sheet").Range("A3"), TableName:="PivotTable1", _
        DefaultVersion:=xlPivotTableVersion10
       
    '****************************************
        Sheets("Summary-Sheet").Select
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Source")
            .Orientation = xlRowField
            .Position = 1
        End With
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("RVP")
            .Orientation = xlRowField
            .Position = 2
        End With
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Director")
            .Orientation = xlRowField
            .Position = 3
        End With
       
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("SalesRep")
            .Orientation = xlRowField
            .Position = 4
        End With

     

    Does that help???

    Tuesday, January 17, 2012 3:41 AM