Extend the Macro to run for all files in a file-folder

[ragav_in]

Dear members,

Currently I am working on collating the number of records from multiple workbooks in a given folder and paste the "workbook name" and "count" in adjacent cells (see below table). What I want to perform is loop though all the files in a given folder, open each workbook (with only one sheet), execute the below code and then get the workbook name and count of records and populate them in another workbook or worksheet (from where the macro is run).

The macro I have shown below currently applies AutoFilter on a certain Column named "Loaded" and filter for records that have "N" as values and count the total number of records with values N. My requirement is to extend this macro to run on a specific folder and perform this macro and populate the workbook name and number of rows in another worksheet.

Sub RecNotLoaded()
Dim i As Integer, rngData As Range, notLoaded As Integer, mysheet As String
Set rngData = Range("A1").CurrentRegion

'To check if the Autofilter is On or Off and Toggle
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
ActiveSheet.Range("A1").AutoFilter

'Perform the autofilter on Column titled "Loaded" and Criteria is "N"
i = Application.WorksheetFunction.Match("Loaded", Range("A1:AZ1"), 0)
rngData.AutoFilter Field:=i, Criteria1:="=N"

'Count the number of rows filtered with this criteria
notLoaded = rngData.Columns(1).SpecialCells(xlCellTypeVisible).count - 1

'get the Worbook Name
mysheet = ActiveWorkbook.Name

'This below code can get the worksheet name and number of rows in pastes in A3 and B3 respectively.
'Sheets("Sheet5").Select
'Range("A3").Value = mysheet
'Range("B3").Value = notLoaded

End Sub

Expectation or Output from the Code is as below

Workbook Name	NotLoaded
US_Workbook1.xlsx	72
EU_Workbook5.xlsx	27
Dubai_Workbook6.xlsx	190
China_Workbook7.xlsx	500

It would be of great help if you can help me to extend this code to perform my task. Now doing it manually is time consuming and sometimes has errors. Would want to avoid such manual errors and also reduce the time required to perform this task which is a daily task with multiple files across the globe.

I thank each and everyone for their time and effort to read though this and provide me with a solution.

Thanks in advance,
ragav_in

 

[DanteAmor]

Adjust in the macro, the name of your sheet to put the results and the name of the folder where you have the files.

Try this:

Sub count_n()
  Dim sh As Worksheet, sh1 As Worksheet
  Dim sPath As String
  Dim sFile As Variant
  Dim wb1 As Workbook
  Dim f As Range
  Dim n As Long, i As Long
  
  Application.ScreenUpdating = False
  
  Set sh = Sheets("Summary")  'Set the name of your sheet
  sPath = "C:\trabajo\files\" 'Set the name of your folder
  
  sh.Rows("2:" & Rows.Count).ClearContents
  sFile = Dir(sPath & "*.xlsx")
  i = 2
  Do While sFile <> ""
    Set wb1 = Workbooks.Open(sPath & sFile)
    Set sh1 = wb1.Sheets(1)
    If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
    Set f = sh1.Range("1:1").Find("Loaded", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      n = WorksheetFunction.CountIf(sh1.Columns(f.Column), "N")
      sh.Range("A" & i).Value = sFile
      sh.Range("B" & i).Value = n
      i = i + 1
    End If
    wb1.Close False
    sFile = Dir()
  Loop

  Application.ScreenUpdating = True
End Sub