On Error goto

[Code Chaser]

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.

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

 

[HackSlash]

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:

[COLOR=#FF0000]
If [COLOR=#FF0000]ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("C").DataRange Is Not Nothing[/COLOR][/COLOR][COLOR=#FF0000] Then
    ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("C").DataRange.Copy[/COLOR] 
    Sheets("recon Pivot").Select
    Range("B" & Column_2_Last_Row + 2).Select
    ActiveSheet.paste
    Application.CutCopyMode = False
End If

 

[RoryA]

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.

 

[RoryA]

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

This is why: On Error WTF? | Excel Matters

 

[Code Chaser]

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

 

[Code Chaser]

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

 

[HackSlash]

Oops,

Try

If ActiveSheet.PivotTables("Recon Pivot").PivotFields("memo 1 Column").PivotItems("A").DataRange.[B]Value[/B] Is Not Nothing Then

and to answer your question you reset the error handler with:

On Error Goto 0