none
Macro suddenly failing RRS feed

  • Question

  • I have a button that launches a macro that refreshes a pivot table and then sets the Filter do a string representing today's date.

    This macro hasn't changed and it started acting up this morning with the error listed below and Excel shutting down and restarting. I haven't changed the macro.

    This morning I was running Office 2010 on Windows 7, this afternoon I've migrated to Office 2013/Win 7 and no change in the failure mode.

    The last update I can find to my system before Office 2013 was KB3025390 on 21.Dec.2014 and I've run the macro lots of times since then.

    This isn't a huge deal, I'm the only sheet user and I think I can set the date filter...but then again, I'm one lazy sob so I like clicking my button...

    Any suggestions are appreciated.

    Doug

    I've searched on the error - no joy...

    Sub refreshPivot()
    '
    ' refreshPivot Macro
    '
        Range("B8").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
        sheetdate = Format(Now(), "m/d/yyyy")

    'Fails on the next row with the error listed below the macro  

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = _
           sheetdate
    End Sub

    Problem signature:
        Problem Event Name: APPCRASH
        Application Name: EXCEL.EXE
        Application Version: 15.0.4649.1000
        Application Timestamp: 53e9a42d
        Fault Module Name: mso.dll
        Fault Module Version: 15.0.4569.1506
        Fault Module Timestamp: 52e0c1d0
        Exception Code: c0000602
        Exception Offset: 0116b30f
        OS Version: 6.1.7601.2.1.0.256.48
        Locale ID: 1033
        Additional Information 1: 1405
        Additional Information 2: 1405378c001fe022d99933a5835dc47c
        Additional Information 3: 6fda
        Additional Information 4: 6fda2bc52abbd6bf4045ec2e053b231a

    Wednesday, January 28, 2015 8:22 PM

Answers

  • Found it.

    I created a fresh sheet with the macro and noticed when I copied data from the old to the new sheet that I had one row with an error and another row that was missing a data. When I pulled those rows, the original sheet macro started working again. Because the raw data is in a table always filtered for 'today', I failed to see the bogus data. This class of data would happen if I started to enter a row, left off the date and other data, then refreshed the table filter.

    I love debugging...juuuuuust love it!

    Thanks for the suggestions, they helped me find the problem.

    • Marked as answer by Dogubob Thursday, January 29, 2015 11:41 AM
    Thursday, January 29, 2015 11:41 AM

All replies

  • Hi Dogubob,

    Which line in the VBA code resulted in this problem?

    Please try these steps:

    1. Try to open this file on another machine, it it works fine on other machines, try to repair the Office application on your machine

    2. Create a new macro-enabled Excel workbook, then copy the original VBA code in it, check if it has the same problem. This will help to determine if the file is damaged or not.

    3. Check the solution in this thread:

    old macro files problem in E15


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 29, 2015 8:21 AM
    Moderator
  • It fails at the line I marked:

    'Fails on the next row with the error listed below the macro  

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").CurrentPage = _
           sheetdate

    I can't do a repair - we don't install software here, packages are pushed to our machines by IT. The code started failing yesterday morning when I was running 2007. It's still failing after IT pulled 2007 and pushed a fresh install of 2010.

    I'll try running the code on a different machine and re-creating the sheet + macro and report back.

    Thank you.

    Thursday, January 29, 2015 11:23 AM
  • Found it.

    I created a fresh sheet with the macro and noticed when I copied data from the old to the new sheet that I had one row with an error and another row that was missing a data. When I pulled those rows, the original sheet macro started working again. Because the raw data is in a table always filtered for 'today', I failed to see the bogus data. This class of data would happen if I started to enter a row, left off the date and other data, then refreshed the table filter.

    I love debugging...juuuuuust love it!

    Thanks for the suggestions, they helped me find the problem.

    • Marked as answer by Dogubob Thursday, January 29, 2015 11:41 AM
    Thursday, January 29, 2015 11:41 AM