Sub CountErrors()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
myExtension = "*.xls*"
myFile = Dir(myPath & myExtension)
Do While myFile <> ""
'Code is working hereinafter. Haven't tested before this point.
Set wb = Workbooks.Open(Filename:=myPath & myFile)
Dim ws As Worksheet
Dim Errors As Long
Dim SheetCount As Integer
Dim i As Long
Dim ErrArray() As Variant
SheetCount = -1
For Each ws In wb.Worksheets
On Error Resume Next
Errors = ws.UsedRange.Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Count
On Error GoTo 0
If Errors > 0 Then
SheetCount = SheetCount + 1
ReDim Preserve ErrArray(SheetCount, 1)
ErrArray(SheetCount, 0) = ws.Name
ErrArray(SheetCount, 1) = Errors
End If
Errors = 0
Next ws
If IsArray(ErrArray) Then
Sheets.Add After:=Sheets(Sheets.Count)
Set ws = ActiveSheet
ws.Name = "Errors"
With Worksheets("Errors")
.Cells(1, 1).Value = "Sheet Name"
.Cells(1, 2).Value = "Error count"
For i = LBound(ErrArray) To UBound(ErrArray)
.Cells(i + 2, 1) = ErrArray(i, 0)
.Cells(i + 2, 2) = ErrArray(i, 1)
Next i
End With
End If
'Haven't tested after this point.
wb.Close SaveChanges:=True
DoEvents
myFile = Dir
Loop
MsgBox "Task Complete!"
ResetSettings:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub