VBA Loop only executes on first file.

[BenGee]

Hi

The below only executes on the first file within the applicable folder and am wondering if someone could help me with this please kindly?

I need it to loop through all open files and apply the stated formulas.

Dim MyFolder As String
Dim MyFile As String
Dim WB As Workbook

Application.ScreenUpdating = False

MyFolder = "\\Test\Data\" & Range("A1")
MyFile = Dir(MyFolder & "\*.xls")
Do While MyFile <> ""
Workbooks.Open filename:=MyFolder & "\" & MyFile
MyFile = Dir
Loop

For Each WB In Workbooks
    If WB.Name <> "AHT" And WB.Name <> "0005*" Then
        Lastrow = Range("C" & Rows.Count).End(xlUp).Row
        Range("H9").Formula = "=E9-D9"
        Range("H9").AutoFill Destination:=Range("H9:H" & Lastrow)
        Range("H9:H" & Lastrow).NumberFormat = "hh:mm:ss"
        Range("I9").Formula = "=SUMIF(C9:C1000,""CALL FOLLOW UP"",H9:H1000)"
        Range("I9").NumberFormat = "hh:mm:ss"
    End If
Next

Application.ScreenUpdating = True

Thanks

 

[Fluff]

Which sheet within each workbook should the formulas be put on?

 

[BenGee]

Within each book, there is only 1 worksheet and each one is ‘Sheet1’.

 

[Fluff]

In that case try

For Each wb In Workbooks
    If wb.Name <> "AHT" And wb.Name <> "0005*" Then
      With wb.Sheets(1)
        LastRow = .Range("C" & .Rows.Count).End(xlUp).Row
        .Range("H9:H" & LastRow).Formula = "=E9-D9"
        .Range("H9:H" & LastRow).NumberFormat = "hh:mm:ss"
        .Range("I9").Formula = "=SUMIF(C9:C1000,""CALL FOLLOW UP"",H9:H1000)"
        .Range("I9").NumberFormat = "hh:mm:ss"
      End With
    End If
Next

 

[BenGee]

This is spot on.

Thank you Fluff, appreciate your help

 

[Fluff]

You're welcome & thanks for the feedback.