On Error goto

Code Chaser

New Member
Joined
Nov 23, 2016
Messages
8
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.


Rich (BB 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 a moderator:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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:
[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
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Oops,

Try
Code:
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:

Code:
On Error Goto 0
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top