How to stop VBA RTE when no data to copy

honkin

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
I have a VBA macro which filters data and copies the results to another sheet. Everything was fine when the football seasons around the world were in full swing, but now with many having completed their interrupted seasons, there is less data. What happens is when there is NO data to transfer, I get an runtime error 1004 message "No cells were found". There are a number of these macros which are called in order, so when this stops, it causes the others to not proceed.

Here is the code:

VBA Code:
Sub FALAYS()
   Dim arr, ws As Worksheet, lc As Long, lr As Long

    arr = Array("L.FAL_19_New_Summer2", "L.FA_FAL_3", "L.FAL_19_New_Summer")

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    With ws.Range("A1", ws.Cells(lr, lc))
        .HorizontalAlignment = xlCenter
        .AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
        If .Rows.Count - 1 > 0 Then
            .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
    Else
            Exit Sub
        End If
    End With
     
    Workbooks("Predictology-Reports.xlsx").Sheets("FAL") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Application.CutCopyMode = False
End Sub

Can anyone see any issues which cause the macro to fail on the copy line? It happens only when there is NO data to copy

Regards
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,441
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...
VBA Code:
If .Rows.Count - 1 > 0 Then
On Error Resume Next
            .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
On Error Goto 0
    Else
            Exit Sub
        End If
 

honkin

Board Regular
Joined
Mar 20, 2012
Messages
145
Office Version
  1. 2016
Platform
  1. MacOS
Maybe...
VBA Code:
If .Rows.Count - 1 > 0 Then
On Error Resume Next
            .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
On Error Goto 0
    Else
            Exit Sub
        End If
Cheers Mark. That seems to do the job well. Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,114,509
Messages
5,548,486
Members
410,839
Latest member
MrSumo85
Top