VBA Type Mismatch error at end of code with loop...

[Ace71425]

Hello see code below.

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

I have this code to loop through and copy other excel files from paths that are in J. It works great BUT it comes up at the end with a type mismatch. Not a big deal but kinda annoying. I think it has to do with the loop finding a blank cell in the next J field. Please let me know if you have a line of code to fix that error and stop the loop when no J is found. Thanks!

 

[RickXL]

Hi,

I am not quite sure what the Dir function is supposed to be doing. So I took it out

Also, I don;t think your With End With statements were doing what you expected. If you specify Range after a With it still uses the activesheet and takes no notice of the With. You need to use .Range then it looks for the preceding With.

So I have made a few changes, including naming the destination sheet as Sheet2 but you can overtype that. I think the various ranges will now all be forced to point to the right Worksheet and Workbook.

Sub RunAllMacros()

    Dim wbSource As Workbook
    Dim wsDest As Worksheet
    Dim row As Integer, os As Integer
    
    Set wsDest = ThisWorkbook.Worksheets("Sheet2")
    
    row = 2
    os = 0
    
    With wsDest
        Do While .Range("J" & row).Value <> ""
            Set wbSource = Workbooks.Open(wsDest.Range("J" & row).Value)
            wbSource.Worksheets(1).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).PasteSpecial
            row = row + 1
        Loop
    End With
    
    Set wbSource = Nothing
    
End Sub

 

[Ace71425]

Man I love this site...thank you so much sir!

 

[RickXL]

No problem - glad you liked it.

Regards,