Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: On Error goto

  1. #1
    New Member
    Join Date
    Nov 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default On Error goto

    I am trying to copy and paste pivot table columns A, B and C into a new sheet and create a new column in the sheet that tell me what pivot table column that row came from, i.e. A,B or C. The problem is not all of the pivot tables I am using contain A B and C columns, sometimes it is a variation of the 3.For instance, when I try and copy column C in the pivot table to the new sheet when there is not a column C in the Pivot table, I get an error. I have tried to use an "On Error Goto" function to skip over the code that copies A, B or C into the new sheet but it only seems to be working once. When I have an A column but no B or C, I get an error when I try and copy the C column from the pivot table.

    Main question: Why is this happening and what do I need to correct it?

    Thanks.


    Code:
    Sub Pivot_Pull_2()
    
    'exclude 031
        On Error Resume Next
        With ActiveSheet.PivotTables("Recon Pivot").PivotFields("CD_BR")
            .PivotItems("31").Visible = False
        End With
    
    'exclude CPC
        With ActiveSheet.PivotTables("Recon Pivot").PivotFields("LOB_SHRT_NM")
            .PivotItems("CPC").Visible = False
        End With
    
    'Make all memo fields visible
        With ActiveSheet.PivotTables("Recon Pivot").PivotFields("Memo 1 Column")
            .PivotItems("A").Visible = True
            .PivotItems("B").Visible = True
            .PivotItems("C").Visible = True
        End With
     On Error GoTo 0
    
    'Create new sheet
        Sheets.Add.Name = "recon Pivot"
        Sheets("Recon Pivot").Move Before:=Sheets(2)
        
    'Paste account data into Recon Pivot (for Memo Column 1)
        Sheets("Pivot by account and memo col").PivotTables("Recon Pivot").RowRange.Copy
        Sheets("Recon Pivot").Select
        ActiveSheet.paste
        Application.CutCopyMode = False
        Range("A1") = "Accounts"
        Range("B1") = "Market Value"
        Range("C1") = "Memo Column"
        Range("D1") = "Memo Row"
        Range("c2") = "PlaceHolder"
        
    'Paste Market Value into recon Pivot (for Memo Column 1)
        Sheets("Recon Pivot").Select
        Dim Column_1_Last_Row As String
        Column_1_Last_Row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("Pivot by account and memo col").Select
        On Error GoTo ErrorCatch1
        ActiveSheet.PivotTables("Recon Pivot").PivotFields("Memo 1 Column").PivotItems("A").DataRange.Copy
        On Error GoTo 0
        Sheets("Recon pivot").Select
        Range("b2").Select
        ActiveSheet.paste
        Application.CutCopyMode = False
    
    'Add appropriate Memo Column (Column 1)
        Sheets("Recon pivot").Select
        Range("c2") = "A"
        Range("c" & Column_1_Last_Row).Select
        Range(ActiveCell, ActiveCell.End(xlUp)).Select
        Selection.FillDown
        
    'Delete Empty Mkt Value Rows (A)
        'Range("B2", "b" & Column_1_Last_Row).Select     Done at the end now
                                            ''''Column 2
    ErrorCatch1:
    'Paste account data into Recon Pivot (for Memo Column 2)
        Sheets("Pivot by account and memo col").PivotTables("Recon Pivot").RowRange.Copy
        Sheets("Recon Pivot").Select
        Range("a" & Column_1_Last_Row + 1).Select
        ActiveSheet.paste
        Application.CutCopyMode = False
        Range("B" & Column_1_Last_Row + 1) = "placeHolder"
        Range("C" & Column_1_Last_Row + 1) = "PlaceHolder"
    
    'Paste Column 2 Mkt Values
        Sheets("Pivot by account and memo col").Select
        On Error GoTo ErrorCatch2
        ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("B").DataRange.Copy
        On Error GoTo 0
        Sheets("recon Pivot").Select
        Range("B" & Column_1_Last_Row + 2).Select
        ActiveSheet.paste
        Application.CutCopyMode = False
    
    ErrorCatch2:
    'Add appropriate memo Column
        Sheets("recon Pivot").Select
        Dim Column_2_Last_Row As String
        Column_2_Last_Row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Range("c" & Column_2_Last_Row).Select
        Range(ActiveCell, ActiveCell.End(xlUp)).Select
        Selection.FormulaArray = "B"
        Rows(Column_1_Last_Row + 1).Delete
        
    'Delete Empty Mkt Value Rows (B)
        'Range("b" & Column_2_Last_Row).Select
        'Range(ActiveCell, "B" & Column_1_Last_Row + 2).Select
        
                                        '''' Column 3
    'Paste account data into Recon Pivot (for Memo Column 3)
        Sheets("Pivot by account and memo col").PivotTables("Recon Pivot").RowRange.Copy
        Sheets("Recon Pivot").Select
        Range("a" & Column_2_Last_Row + 1).Select
        ActiveSheet.paste
        Application.CutCopyMode = False
        Range("B" & Column_2_Last_Row + 1) = "placeHolder"
        Range("C" & Column_2_Last_Row + 1) = "PlaceHolder"
     
     
    'Paste Column 2 Mkt Values
        Sheets("Pivot by account and memo col").Select
        On Error GoTo ErrorCatch3
        ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("C").DataRange.Copy 
    On Error GoTo 0
        Sheets("recon Pivot").Select
        Range("B" & Column_2_Last_Row + 2).Select
        ActiveSheet.paste
        Application.CutCopyMode = False
        
    'Add appropriate memo Column
        Sheets("Recon Pivot").Select
        Dim Column_3_Last_Row As String
        Column_3_Last_Row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Range("c" & Column_3_Last_Row).Select
        Range(ActiveCell, ActiveCell.End(xlUp)).Select
        Selection.FormulaArray = "C"
        Rows(Column_2_Last_Row + 1).Delete
        
    'Delete Empty Mkt Value Rows (B)
        Range("b" & Column_3_Last_Row).Select
        Range(ActiveCell, "B" & Column_2_Last_Row + 2).Select
    
    ErrorCatch3:
    'delete Empty Mkt Value Rows Pt.2
    On Error Resume Next
        Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
    'Fill Memo Row with file's name
        Sheets("Recon Pivot").Select
        Range("D2") = "i"                                  'File name
        Dim Memo_Row_Fill As String
        Memo_Row_Fill = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Range("D" & Memo_Row_Fill).Select
        Range(ActiveCell, ActiveCell.End(xlUp)).Select
        Selection.FillDown
           
           
    End Sub
    Last edited by RoryA; Nov 23rd, 2016 at 12:37 PM. Reason: Added code tags.

  2. #2
    Board Regular HackSlash's Avatar
    Join Date
    Nov 2016
    Location
    The Office
    Posts
    360
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error goto

    Avoid using On Error whenever possible. Never use "On Error Resume Next" because it could create very strange results.

    You want to test for every condition that could cause an error. If you know that your error is due to your data missing you should be able to test for that.

    Something like:

    Code:
    
    If ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("C").DataRange Is Not Nothing Then
        ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("C").DataRange.Copy 
        Sheets("recon Pivot").Select
        Range("B" & Column_2_Last_Row + 2).Select
        ActiveSheet.paste
        Application.CutCopyMode = False
    End If
    Last edited by HackSlash; Nov 23rd, 2016 at 12:21 PM.

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error goto

    Quote Originally Posted by HackSlash View Post
    Never use "On Error Resume Next" because it could create very strange results.
    I'd have to disagree with that. Although OERN is often misused, it's actually very useful when used properly - i.e. with as limited a life as possible - and occasionally it's unavoidable.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,351
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: On Error goto

    Quote Originally Posted by Code Chaser View Post
    Main question: Why is this happening and what do I need to correct it?
    This is why: On Error WTF? | Excel Matters

  5. #5
    New Member
    Join Date
    Nov 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error goto

    What you are saying makes sense, and that would be a great way to test for it, but I keep getting "Compile error: Invalid use of Object"

    If ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("A").DataRange Is Not Nothing Then
    ActiveSheet.PivotTables("Recon Pivot").PivotFields("Memo 1 Column").PivotItems("A").DataRange.Copy
    Sheets("Recon pivot").Select
    Range("b2").Select
    ActiveSheet.paste
    Application.CutCopyMode = False
    'Add appropriate Memo Column (Column 1)
    Sheets("Recon pivot").Select
    Range("c2") = "A"
    Range("c" & Column_1_Last_Row).Select
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
    Selection.FillDown

    'Delete Empty Mkt Value Rows (A)
    'Range("B2", "b" & Column_1_Last_Row).Select Done at the end now
    End If

  6. #6
    New Member
    Join Date
    Nov 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error goto

    Thanks for the help, how do I "reset error handling" in my code though?

    On Error GoTo ErrorCatch2
    ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("B").DataRange.Copy
    resume next '''<--- will this reset the error handler so that I new error can be picked up... (I am confused on this whole concept)
    Sheets("recon Pivot").Select
    Range("B" & Column_1_Last_Row + 2).Select
    ActiveSheet.paste
    Application.CutCopyMode = False

  7. #7
    Board Regular HackSlash's Avatar
    Join Date
    Nov 2016
    Location
    The Office
    Posts
    360
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: On Error goto

    Oops,

    Try
    Code:
    If ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("A").DataRange.Value Is Not Nothing Then
    and to answer your question you reset the error handler with:

    Code:
    On Error Goto 0

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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