none
MACRO - pivot items filtering problem RRS feed

  • Question

  • Hi there,

    I have a macro as follows:

    With ActiveSheet.PivotTables("PivotTable_FP1").PivotFields("Source Of Posting Code")
    .PivotItems("MANUAL").Visible = False
    End With

    For this Pivot Table, by months, sometimes there is MANUAL but sometimes MANUAL is not exist. I mean, under the column "Source of Posting Code", "Manual" may appears in June 2012 but not in July 2012. So if I run this macro monthly, it may appear an error saying that the pivot item is not found since it appears irregularly..

    I have tried ---
    On error resume next
    With ActiveSheet.PivotTables("PivotTable_FP1").PivotFields("Source Of Posting Code")
    .PivotItems("MANUAL").Visible = False
    End With

    With this, I can successfully ignore the error but all other errors are disabled as well and this is not what I want
    What I want is just suppress THIS error but not the rest
    (I am afraid I cannot spot the other errors if I use this. Then the data generated may be wrong -- I still have other codes in this MACRO)

    Does there anyone have any ideas? Thanks in advance!

    Friday, August 10, 2012 6:42 AM

Answers

  • On error resume next
    With ActiveSheet.PivotTables("PivotTable_FP1").PivotFields("Source Of Posting Code")
    .PivotItems("MANUAL").Visible = False
    End With

    On error Goto 0

    Actually On Error Goto 0 should be used imediately when you do not want the error to occur.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.


    Friday, August 10, 2012 6:51 AM
    Answerer

All replies

  • On error resume next
    With ActiveSheet.PivotTables("PivotTable_FP1").PivotFields("Source Of Posting Code")
    .PivotItems("MANUAL").Visible = False
    End With

    On error Goto 0

    Actually On Error Goto 0 should be used imediately when you do not want the error to occur.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.


    Friday, August 10, 2012 6:51 AM
    Answerer
  • OMG this is so simple and directly hit my problem

    thanks it is solved now you are the man!

    Friday, August 10, 2012 7:09 AM
  • and now my code is

    .
    .
    .
    .

    On error resume next
    With ActiveSheet.PivotTables("PivotTable_FP1").PivotFields("Source Of Posting Code")
    .PivotItems("MANUAL").Visible = False
    End With
    On error Goto 0
    .
    .
    .
    .
    .
    .

    I have checked, the line below "On Error Goto 0" can pop up run-time error and that is what i want
    Thanks!

    btw, not exactly know what you mean by "Actually On Error Goto 0 should be used imediately when you do not want the error to occur."

    Friday, August 10, 2012 7:11 AM
  • VBA has it's own error trapping method and way to alert user.But it will only provide user with Some number and small description.It is not very user friednly.

    So,sometimes we may prefer to handle the "alerting the user" in the Code itself so that errors can be presented to user with more helpful way.

    We use On Error Resume Next - To Ignore , Resume our_Label - To handle in code etc.

    we use On Error Goto 0 - To ask VBA to start it's own way of informing user.

    After Resume Next all errors are ignored including that error which you prefer to ignore.Which is not desirable.Goto 0 instructs VBA to catch error again. 

    In your case you simply do not need to know whether it's there or not.You want it to be not visible.So if it is not in PivotItems collection you do not bother.That's why you use On Error Resume Next.

    I was not sure if the sample is entire code for with..end with or some more statement is there before end with.That's why I mentioned that you should use when you want the VBA to handle errors.

    Hope this helps.Anyway you can get lot of details from VBA offline help.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 10, 2012 8:21 AM
    Answerer
  • Wow thats so enlightening!
    Thank you and I got it now!

    Friday, August 10, 2012 8:42 AM