Stop 400 error when code fails

[Ace71425]

So I have a code that copies over data from other workbooks i wanted a button to do so thus I have a runallmacros command that my code is contained with there aren't always as many books to be copied as I put in the code so the code works perfectly but when it jumps to the next command and finds that there is no file path it returns error 400 ...no big deal but I'm a perfectionist...is there a line of code I can put in that's basically like if this block of code can't run end macro and stop trying to move further through the code? If that doesn't make sense I'll post my code just let me know...thanks!

 

[Norie]

If the problem is that a file can't be found you can use Dir to check for the file's existence before running the code.

 

[Ace71425]

The way the sheet works is there is a button that allows for the files to be selected in a file window and then those filepaths are copied to a column. Then the code uses the path from that column. Therefore the problem is basically the code is like hey that column is blank I have no file path.

Sub RunAllMacros()
Procedure1
Procedure2
Procedure3
Procedure4
Procedure5
Procedure6
Procedure7
Procedure8
Procedure9
Procedure10
Procedure11
Procedure12
Procedure13
Procedure14
Procedure15
Procedure16
Procedure17
Procedure18
End Sub

Sub Procedure1()

Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks.Open(Range("J2").Value)
Set wbTarget = ActiveWorkbook
With wbSource.Sheets("Work").Select
Range("A5:E500").Copy

Application.DisplayAlerts = False
wbSource.Close

Range("A" & Rows.Count).End(xlUp).Offset(0).Select
ActiveSheet.Paste

End With
End Sub


Sub Procedure2()

Dim wbSource As Workbook
Dim wbTarget As Workbook
Set wbSource = Workbooks.Open(Range("J3").Value)
Set wbTarget = ActiveWorkbook
With wbSource.Sheets("Work").Select
Range("A5:E500").Copy

Application.DisplayAlerts = False
wbSource.Close

Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

End With
End Sub

This code runs all the way down to 18. But there may only be 10 files.

 

[Big Wade]

Assuming procedures 1 - 18 are all exactly the same, then you can replace all of the code with something like this.

Sub RunAllMacros()
Dim wbSource As Workbook
Dim row As Integer, os As Integer
row = 2
os = 0
Do While Dir(Range("J" & row).Value) <> ""
    Set wbSource = Workbooks.Open(Range("J" & row).Value)
    With wbSource.Sheets("Work").Select
        Range("A5:E500").Copy
        Application.DisplayAlerts = False
        wbSource.Close
        Application.DisplayAlerts = True
        If Range("A1").Value <> "" Then os = 1
        Range("A" & Rows.Count).End(xlUp).offset(os).Select
        ActiveSheet.Paste
    End With
    row = row + 1
Loop
Set wbSource = Nothing
End Sub

 

[Norie]

You can easily check if a cell is blank and you could also easily reduce all those subs to one sub.

I'm not at a computer right now but I'll post something shortly.

 

[Ace71425]

BigWade that code is awesome but it comes up with a type mismatch after the last file is copied over at least the code is condensed but now I still have an error message at the end any ideas?