On error not working

[Assamita]

Hi guys,

I have a code to create a Pivot table. A piece of the code orders the columns in a specific order, but it used to fail if there wasn't values of some of the items, so I added the "On error resume next" statement. However, it seems not to be working. This is the code:

    On Error Resume Next


    With ActiveSheet.PivotTables("Dashboard").PivotFields("Ageing")
        .Orientation = xlColumnField
        .Position = 1
        .PivotItems("Btw 0 and 7 d.").Position = 1
        .PivotItems("Btw 8 and 14 d.").Position = 2
        .PivotItems("Btw 15 and 30 d.").Position = 3
        .PivotItems("Btw 31 and 45 d.").Position = 4
        .PivotItems("Btw 46 and 60 d.").Position = 5
        .PivotItems("Btw 61 and 90 d.").Position = 6
        .PivotItems("More than 90 d.").Position = 7
    End With

Any idea why could this be happening?

Thank you

 

[RoryA]

What exactly does "not working" mean? If you're still seeing an error message from that code, then you either have error handling set to break on all errors in your VB Editor settings, or your code is already in an error state when it gets to this section.

 

[Assamita]

Sorry, by "not working" I mean that if the "Btw 15 and 30 d.", for example, is missing, it should skip to the next one and go on with the macro, but instead, it triggers a VBA error message. I don't have a screenshot at the moment, and this macro runs automatically once a day. I'll try to get the next error screenshot and post it here.

I'm thinking that maybe the problem is that if there's not data for the 7 items, therefore the pivot won't have 7 columns at all, and then it fails because I'm trying to give it an order value that it's not possible. I still think it should skip the step and go on, but anyways, I'll try with a loop.

Thank you

 

[RoryA]

You shouldn't see a message unless something else is going on, like the two options I mentioned.