ragav_in
Board Regular
- Joined
- Feb 13, 2006
- Messages
- 89
- Office Version
- 365
- 2016
- Platform
- Windows
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.
Expectation or Output from the Code is as below
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
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.
VBA Code:
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