If error end vba code

[Nanogirl21]

I am using the code to move data from 1 worksheet to another. Right now the code skips to the next worksheet if there is an error (the error is when there is no data to move).

How can I modify the code to skip to End Sub and not go to next worksheet if there is an error WITHOUT getting the error/debug pop up?

Dim x As Long
Dim y As Long
Dim cell As Range
    
For Each ws In Worksheets
On Error Resume Next
        
Dim Usdrws As Long
    
Usdrws = Sheets("TVAE").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
Sheets("TVAE").Range("A1:A" & Usdrws).SpecialCells(xlVisible).Copy
Sheets("Tracking").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
Sheets("TVAE").Range("B1:B" & Usdrws).SpecialCells(xlVisible).Copy
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
Sheets("TVAE").Range("C1:K" & Usdrws).SpecialCells(xlVisible).Copy
Sheets("Tracking").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
Sheets("TVAE").Range("M1:O" & Usdrws).SpecialCells(xlVisible).Copy
Sheets("Tracking").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
Sheets("TVAE").Range("R1:U" & Usdrws).SpecialCells(xlVisible).Copy
Sheets("Tracking").Range("V" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

Next ws

End Sub

 

[Leith Ross]

Hello Nanogirl21,

Try this...

    Dim x       As Long
    Dim y       As Long
    Dim cell    As Range
    Dim Usdrws  As Long
    
        On Error GoTo ErrHandler
        
        For Each ws In Worksheets
            Usdrws = Sheets("TVAE").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
            Sheets("TVAE").Range("A1:A" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("B1:B" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("C1:K" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("M1:O" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("R1:U" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("V" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Next ws
        
ErrHandler: ' Transfer execution to here when there is an error.
        
End Sub

 

[Norie]

Try this.

Dim ws As Worksheet
Dim x As Long
Dim y As Long
Dim cell As Range
Dim Usdrws As Long

    For Each ws In Worksheets
    
        On Error GoTo ErrHandler

        Usdrws = Sheets("TVAE").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        Sheets("TVAE").Range("A1:A" & Usdrws).SpecialCells(xlVisible).Copy
        Sheets("Tracking").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

        Sheets("TVAE").Range("B1:B" & Usdrws).SpecialCells(xlVisible).Copy
        Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

        Sheets("TVAE").Range("C1:K" & Usdrws).SpecialCells(xlVisible).Copy
        Sheets("Tracking").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

        Sheets("TVAE").Range("M1:O" & Usdrws).SpecialCells(xlVisible).Copy
        Sheets("Tracking").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

        Sheets("TVAE").Range("R1:U" & Usdrws).SpecialCells(xlVisible).Copy
        Sheets("Tracking").Range("V" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

    Next ws

ErrHandler:
    If Err.Number <> 0 Then
        MsgBox "There's been a problem!"
    End If
    
End Sub

 

[Nanogirl21]

worked. thank you.

 

[Leith Ross]

Hello Nonogirl21,

You're welcome. It is also possible to handle specific errors, ones you know will happen with no message, and show a message for those that aren't expected.

 

[Nanogirl21]

Hello Nonogirl21,

You're welcome. It is also possible to handle specific errors, ones you know will happen with no message, and show a message for those that aren't expected.

How would you write it if there is a different error? The expect error and the only error that I can see happening is the worksheet is blank.

 

[Leith Ross]

Hello Nanogirl21,

Here is how you can display the error number and message for unexpected errors. An error number of 0 (zero) means there is no error. Error 91, "Object variable or With block variable not set", is expected when the Find operation fails to find any data on the sheet. Any other error will display an alert.

Sub Example1()


    Dim x       As Long
    Dim y       As Long
    Dim cell    As Range
    Dim Usdrws  As Long
    
        On Error GoTo ErrHandler
        
        For Each ws In Worksheets
            Usdrws = Sheets("TVAE").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
            Sheets("TVAE").Range("A1:A" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("B1:B" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("C1:K" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("M1:O" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    
            Sheets("TVAE").Range("R1:U" & Usdrws).SpecialCells(xlVisible).Copy
            Sheets("Tracking").Range("V" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Next ws
        
ErrHandler: ' Transfer execution to here when there is an error.
        Select Case Err.Number
            Case 0, 91
                ' Ignore these error numbers because they are expected.
            Case Else
                MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf & Err.Description
        End Select
        
End Sub