Is there a way to skip error "filename is not found" and move to the next file

Question

Is there a way to skip error "filename is not found" and move to the next file?

Sub CopyDataAndMoveDown()

Application.ScreenUpdating = False

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim rngToCopy As Range, rngToPaste As Range
Dim x As Long
Dim breakdown1
Dim breakdown As Worksheet: Set breakdown = wb.ActiveSheet

For x = 4 To 504 Step 6

    With wb.Sheets("Sheet1")
        breakdown1 = breakdown.Cells(9, x - 2)
    End With

    If IsEmpty(breakdown1) Then
        Call MoveBelow
    Else

        With wb.Sheets("Sheet1")
            Set rngToCopy = .Range(.Cells(4, x - 2), .Cells(24, x + 3))
            Debug.Print rngToCopy.Address
        End With

        With wb.Sheets("Sheet2")
            Set rngToPaste = .Range(.Cells(4, x - 2), .Cells(rngToCopy.Rows.Count + 3, x + 3))
            Debug.Print rngToPaste.Address
        End With

        rngToPaste = rngToCopy.Value
    End If

Next x

Application.ScreenUpdating = True
MsgBox "Valmis."
End Sub

Sub MoveBelow ()

Application.ScreenUpdating = False

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.ActiveSheet
Dim rngToCopy As Range, rngToPaste As Range
Dim x As Long
Dim breakdown1
Dim breakdown As Worksheet: Set breakdown = wb.ActiveSheet

For x = 4 To 504 Step 6

    With wb.Sheets("Sheet1")
        breakdown1 = breakdown.Cells(9, x - 2)
    End With

    If IsEmpty(breakdown1) Then
        ' At this point when the macro meet again a empty cell
        ' it should keep moving from the same counted X
        ' but start the paste operation from 24 rows below.
    Else

        With wb.Sheets("Sheet1")
            Set rngToCopy = .Range(.Cells(4, x - 2), .Cells(24, x + 3))
            Debug.Print rngToCopy.Address
        End With

        With wb.Sheets("Sheet2")
            Set rngToPaste = .Range(.Cells(28, x - 2), .Cells(rngToCopy.Rows.Count + 3, x + 3))
            Debug.Print rngToPaste.Address
        End With

        rngToPaste = rngToCopy.Value
    End If

Next x

Application.ScreenUpdating = True
MsgBox "Valmis."
End Sub

So when the macro is copying / pasting data from Sheet 1 to Sheet 2 and meets an empty cell it should keep going, copying next available data, but paste it 24 rows below.

--------Below the old question.
I have a VBA which is opening and closing file for that INDEX function get data. My problem is that. VBA is getting the filename from reference cell which contain the full path. But some of the reference cells are blanks/zeros and then the running VBA stops and give me error "filename is not found". Is there a way to skip that and move to next step?

Sub HaeReseptiTiedot()

Dim myfile As String
Dim myfile1 As String
Dim myfile2 As String
Dim myfile3 As String
Dim myfile4 As String
Dim myfile5 As String
Dim myfile6 As String
Dim myfile7 As String
Dim myfile8 As String
Dim myfile9 As String


myfile = Cells(19, 4).Value
myfile1 = Cells(19, 9).Value
myfile2 = Cells(19, 14).Value
myfile3 = Cells(19, 19).Value
myfile4 = Cells(19, 24).Value
myfile5 = Cells(19, 29).Value
myfile6 = Cells(19, 34).Value
myfile7 = Cells(19, 39).Value
myfile8 = Cells(19, 44).Value
myfile9 = Cells(19, 49).Value

Application.ScreenUpdating = False


Workbooks.Open Filename:=myfile, UpdateLinks:=0
ActiveWorkbook.Close False
Sheets("Aputaulukko 2").Select
Range("D16:G30").Select
Selection.Copy
Sheets("Aputaulukko 3").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Workbooks.Open Filename:=myfile1, UpdateLinks:=0
ActiveWorkbook.Close False
Sheets("Aputaulukko 2").Select
Range("I16:L30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Aputaulukko 3").Select
Range("G4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Answer 1

The best way I have found to handle this is to use the "On Error" statement. You can keep it really simple and use On Error Resume Next, which tells the code to skip the error entirely and move to the next statement (that does not have an error). The main issue with this is that it covers ALL errors, not just the specific one you are having issues with currently. It can make it hard to know if errors are occurring/if your code is functioning as you expect.

The other option, which can help avoid the issues mentioned above, is to use something like this:

On Error GoTo ErrH
    'Main Body of Your Code
    Exit Sub 'Use to avoid continuing on to the ErrH section.
ErrH:
    'Some method for handling the error, such as a message box or other notification.

This usually isn't necessary with small chunks of code, but when you start combining your subs and functions it can be a life saver!

Good Luck!

Edit: You could/should also consider removing those blanks if they are not necessary for the sheet to work.

Answer 2

I took the liberty to rewrite your code... i'm still not quite sure why you are openning and closing the workbook immediately, but in essence this is what your code does at the moment:

Option Explicit

Sub HaeReseptiTiedot()

Application.ScreenUpdating = False

Dim wbSource As Workbook
Dim wb As Workbook: Set wb = ThisWorkbook 'Or ActiveWorkbook or Workbooks("book name")
Dim ws As Worksheet: Set ws = wb.ActiveSheet 'Or wb.Sheets("Sheet Name")
Dim rngToCopy As Range, rngToPaste As Range
Dim X As Long

For X = 4 To 49 Step 5
    On Error Resume Next
    Set wbSource = Workbooks.Open(FileName:=ws.Cells(19, X), UpdateLinks:=0)
    On Error GoTo 0

    If Not wbSource Is Nothing Then
        wbSource.Close False

        With wb.Sheets("Aputaulukko 2")
            Set rngToCopy = .Range(.Cells(16, X), .Cells(30, X + 3))
            'Debug.Print rngToCopy.Address
        End With

        With wb.Sheets("Aputaulukko 3")
            Set rngToPaste = .Range(.Cells(4, X - 2), .Cells(rngToCopy.Rows.Count + 3, X + 1))
            'Debug.Print rngToPaste.Address
        End With

        rngToPaste = rngToCopy.Value
    End If
    Set wbSource = Nothing
Next X

Application.ScreenUpdating = True
End Sub

Answer 3

Here is a function that can check if a file exists:

'********************************************************************************************************************************
' To check if a particular file exists
' Set excelFile = False, if it is not an Excel file that is being checked
'********************************************************************************************************************************
Public Function isAnExistingFile(ByVal fileNameStr As Variant, Optional ByVal excelFile As Boolean = True) As Boolean
Dim wb As Workbook

isAnExistingFile = True
Err.Clear
On Error GoTo errHandler
If Not VarType(fileNameStr) = vbString Then
    isAnExistingFile = False
ElseIf Len(fileNameStr) = 0 Then
    isAnExistingFile = False
ElseIf Len(Dir(fileNameStr)) = 0 Then
    isAnExistingFile = False
ElseIf ((GetAttr(fileNameStr) And vbDirectory) <> vbDirectory) = False Then
    isAnExistingFile = False
Else
    If excelFile Then
        On Error Resume Next
        Set wb = Application.Workbooks.Open(Filename:=fileNameStr, UpdateLinks:=0, ReadOnly:=True)
        If wb Is Nothing Then isAnExistingFile = False
        If Not wb Is Nothing Then
            wb.Close False
            Set wb = Nothing
        End If
        GoTo Out
    End If
End If

errHandler:
If Not Err.Number = 0 Then isAnExistingFile = False

Out:
Err.Clear: On Error GoTo 0

End Function

Answer 4

You could work around this by creating a second Sub that opens the file and handles the error if the file doesn't exist. That way you are still able to catch other Errors in the main Sub without going to next. Example:

Sub MainSub()

    myFile1 = "C:\Temp\New1.xlsx"
    myFile2 = "C:\Temp\New2.xlsx"
    CheckAndOpen (myFile1)
    CheckAndOpen (myFile2)

End Sub

Sub CheckAndOpen(myFileName As String)

    On Error Resume Next
    Workbooks.Open Filename:=myFileName
    Debug.Print Err.Number, myFileName

End Sub

Answer 5

You could, alternatively, just put the following in your code:

If dir("FILENAME") <> "" Then
 Add the rest of your code
End If

I usually run 3 or 4 for loops inside of each other with different variables to get the full path of each file, then put this to ensure I do not open files where there are blanks.