Run-time error 5

[DavidC78]

Hi,

I hope someone can help.

I am trying to create a command within a master document that will loop through a folder and extract values from specific cells and paste them to the master document. The folder has hundreds of individual files, all named differently, and each file has multiple worksheets but all worksheets are in the same format so cell references are uniform. I have managed to create something which I think should work but unfortunately I have been getting the Run-time error 5 at the point where the information is to be transferred:

Sub LoopThroughLogs()

Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("T:\ThinkTank\CENTRAL FILES\LOGS\RETURNED LOGS\")

y = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

For Each wbFile In fldr.Files

y = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

If fso.GetExtensionName(wbFile.Name) = "xls" Then
   
    Set wb = Workbooks.Open(wbFile.Path)
   
    For Each ws In wb.Sheets
       
    ThisWorkbook.Sheets("Sheet1").Cells(y, 2) = ws.Cells("F4")       '''HERE IS WHERE THE RUN-TIME ERROR INTERUPTS THE COMMAND
    ThisWorkbook.Sheets("Sheet1").Cells(y, 3) = ws.Cells("F6")
    ThisWorkbook.Sheets("Sheet1").Cells(y, 4) = ws.Cells("B11")
    ThisWorkbook.Sheets("Sheet1").Cells(y, 5) = ws.Cells("I11")
    y = y + 1

    Next ws
       
    wb.Close
   
End If

Next wbFile

End Sub

If anyone has any ideas that might help I'd be very grateful.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
Try

For Each ws In wb.Worksheets

 

[DavidC78]

Hi & welcome to MrExcel.
Try

For Each ws In wb.Worksheets

Thanks for the quick response.

I just gave that a try and unfortunately it still returns the same error

 

[Fluff]

This

ws.Cells("F4")

should be

ws.Range("F4").value

you will need to make that change to the other rows.

 

[DavidC78]

This

ws.Cells("F4")

should be

ws.Range("F4").value

you will need to make that change to the other rows.

Absolutely spot on, that has worked a treat.

Thanks for the help Fluff, it's much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.