I have 3 Excel files that have in the column B the comments: ok or check. Column C contains the product numbers of the products that are either ok or need to be checked. I want to create a list (an overview) in another Excel file (4) of all the products that have the word "check" in column B in the other three Excel files (1,2,3).

I cannot use a pivot table in the three excels because it has to be refreshed manually. Filter is also not an option. I would like to use VBA/Macros in Excel.

Excel 1
Status  Product number          
check   1254968         
ok      5541485         
check   2153654         
ok      4588999         
ok      8954668         
ok      6945665         
check   7469968         
check   6665448         
Excel 2
Status  Product number          
ok      7455561         
ok      5145684                 
ok      4589666                 
check   4896471                 
check   1117347                 
check   5656478                 
ok      5256488
Excel 3
Status  Product number          
ok      3389741         
check   6754889         
check   1489798         
ok      6489646     
Excel 4
Products to check

I expect to have a list with all the product numbers that need to be checked in my 4th. Excel.

1 Answers

Kolin Chang On

Create a new workbook in the same folder as the other files are located. Please consider to move away any other .xlsx files before running this macro :) If you need to run it in a specific folder and you are not able to move the files, please include a condition based on the name of the files that you do want to use. Otherwise, the below should be sufficient. Please read all comments in the code.

Sub test()

Dim wb1, wb2 As Workbook
Dim HeadSet As Boolean

Set wb1 = ThisWorkbook

FolderName = "your/path/" 'full path name to folder where xlsx is located
file_name = Dir(FolderName & "*" & ".xlsx", vbDirectory) 'assuming the files are all  .xlsx

HeadSet = False 'for fun

'for each file in FolderName
Do While Right(file_name, 5) = ".xlsx" And file_name <> ""
    'open workbook
    Set wb2 = Workbooks.Open(file_name, False, True)
        With wb2.Sheets(1)
            For i = .Range("B1").End(xlDown).Row To .Range("B20000").End(xlUp).Row 'change .sheets(1) 1 = index to > your index, or "sheetname"
                If LCase(Trim(.Range("B" & i).Value)) = "check" Then 'checks lowercase, so condition should be lower
                    'create headers in output sheet
                    If HeadSet = False Then
                        wb1.Sheets(1).Range("A1").Value = "Products"
                        wb1.Sheets(1).Range("A1").Value = "Result of check"
                        HeadSet = True
                    End If
                    'change wb1.sheets(index) to your index, or the sheet name between ""
                    wb1.Sheets(1).Range("A" & wb1.Sheets(1).Range("A20000").End(xlUp).Row + 1).Value = .Range("C" & i).Value
                    wb1.Sheets(1).Range("B" & wb1.Sheets(1).Range("A20000").End(xlUp).Row + 1).Value = .Range("B" & i).Value
                End If
            Next i 'next iteration
        End With
    wb2.Close False 'close workbook

    file_name = Dir 'go to next file

End Sub