Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Debugging a pivot table problem (2003)

    Hi all,
    Thanks to the help on this board I received some code which updates the information in a pivot table automatically when the data in the main table is changed. The VBA code has been working well for weeks, but for some reason I have started to get debugging errors. When I select the option to debug, a VBA window comes up and the following code is displayed:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A1:P5904")) Is Nothing Then
    Worksheets("Totals Table").PivotTables(1).PivotCache.Refresh
    End If
    End Sub

    The following line is highlighted yellow, and I am assuming that this means that there is an error in that line:

    Worksheets("Totals Table").PivotTables(1).PivotCache.Refresh

    The problem is that I cannot seem to find a fault in this line. (Screen shot attached)
    Anyone have any ideas how I should solve the problem?
    Thanks.

    Bill

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

    Re: Debugging a pivot table problem (2003)

    When asking for assistance, try to provide all relevant information. You haven't told us what the error message says, so it is hard to help.

    Not knowing the error message, we are left to guess. Some obvious causes for an error message would be
    - There is no pivot table on the Totals Table sheet.
    - The Totals Sheet table has been protected.

    As an alternative, you could try

    Worksheets("Totals Table").PivotTables(1).RefreshTable

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Debugging a pivot table problem (2003)

    My apologies Hans, I wasn't really sure how to outline the problem.
    I have attached a screenshot of the error message which may assist. Additionally I cut and pasted the line of code you suggested but unfortunately that didn't help.
    Looking at the error message it would seem that there is a problem with a field name, but I can't seem to find it if that is the case.
    Any help much appreciated.

    Bill

  4. #4
    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: Debugging a pivot table problem (2003)

    It would be easier if you attached a spreadsheet rather than a screen shot. You can remove any proprietary info...

    Steve

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

    Re: Debugging a pivot table problem (2003)

    Without seeing the workbook, it's impossible to say what causes this. Did the structure of the source data for the pivot table change (columns added or removed)?

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Debugging a pivot table problem (2003)

    Sorry, I'm not having a good day. I don't know how the screen shot got onto the message like that, I just inserted a file as normal (I thought).
    Unfortunately I can't attach a copy of the spreadsheet as removing any proprietry data would remove any chance of anyone understanding the problem.

    Hans - Yes there has been columns added to the source data, but not columns that include the source data for the pivot table. There was a column added next to the source data. Could that be the problem?

    Thanks so much.

    Bill

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Debugging a pivot table problem (2003)

    Bingo! Problem solved.
    Someone had added a space to one of the field (Column) names! All is working now. Thanks all for your guidance.

Posting Permissions

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