Copy Data from multiple random named workbooks to one master workbook if certain criteria is met.

[jpena418]

First post but really need help. We are doing COVID research and obtain a lot of information back from our testing customers. We obtain a folder that contains lots of data and upwards of 1000 .csv files. The files that I am interested in are all named *results.csv There are other files in the folder but I can ignore those.

I need to open each file that ends in *results.csv and look in column B between rows B210-B260 for the term No Call.

If the term No Call is present in any one of those cells then I need to take the data in row B6, G6, D6, F7, L7, R7, F6 and copy it to a master workbook and then move to the next *results.csv workbook and do the same thing and save the same data if No Call is present. If No Call is not present I can close the workbook and move to the next *results.csv file.

I can attach file examples if necessary. Right now we are manually searching and entering this data and it is taking days to complete. My company has not deemed this a priority so I am trying to automate it if I can and using VBA seems like the solution but I am a total beginner.

Any help is appreciated.

 

[JLGWhiz]

the data in row B6, G6, D6, F7, L7, R7, F6 and copy it to a master workbook

Where in the master workbook do you want the data pasted? Do you want it pasted in consecutive cells in the same order as shown above of in noncontiguous cells? If noncontguous specify which copied cells to which destination column. Also, if there is more than one sheet in a workbook, specify the sheet name of the workbook that you want to copy from and the sheet name you want to copy to.

It is assumed the code would be run from the master workbook and that the master workbook would be in the same directory as the "results" files. If not, you will need to provide the path(s) that apply to the workbooks.

 

[jpena418]

The master workbook will live in each folder with the data. There will only be one master per folder and then a new one will be created for the next customer and their data set.
It should look like this:

B6, G6, D6, F7, L7, R7, F6
B6, G6, D6, F7, L7, R7, F6
B6, G6, D6, F7, L7, R7, F6

where data is pulled from each *results.csv file that contained "No Call" in B210-B260

I have been playing with Power Query this morning but still making little progress.

 

[JLGWhiz]

that only partially answered the questions, but try the code below. Copy the code to code module1. If you do not currently have a code module1 and the code window is dark when you press Alt + F11, then click 'Insert' on the vb editro tool bar then click 'Module'. The window should then brighten and you can paste the code into that window.

Sub t()
Dim wb As Workbook, fPath As String, fName As String, sh As Worksheet, ws As Worksheet, ary As Variant, cpy As Variant
fPath = ThisWorkbook.Path & "\"
fName = Dir(fPath & "*results.csv")
Set sh = ThisWorkbook.Sheets(1) 'replace the 1 with sheet name in quotes
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
            Set ws = wb.Sheets(1)
            With ws
                ary = Array(.Range("B6").Value, .Range("G6").Value, .Range("D6").Value, .Range("F7").Value, _
                    .Range("L7").Value, .Range("R7").Value, .Range("F6").Value)
            End With
            If Application.CountIf(ws.Range("B210:B260"), "No Call") > 0 Then
                sh.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 7) = ary
            End If
        End If
        wb.Close False
        Set wb = Nothing
        Set ws = Nothing
        fName = Dir
    Loop
End Sub

 

[jpena418]

Thank you JLGWhiz. I tried your code but it just flashed my screen and locked up my PC. After reviewing I think the part I left out is the Sheet name. Each Workbook will have a different sheet name in it that looks like it matches the Workbook name. See attached.

Also, sorry if I didnt answer your questions, please let me know what I missed and I will clarify. I can also upload this to dropbox if it would help.

 

[JLGWhiz]

The CSV files do automatically name the worksheet the same as the workbook, but the master workbook would not necessarily have sheet 1 named the same because it should be a .xlsm vile instead of .csv. The code ran OK in my test set up which was to open a .csv file from the same tirectory as the workbook hosting the code. The code rand as expected and pasted the appropriate data into the next available row of the master file. I cfannot duplicated the condition you describe for when you ran the code, so I have no further suggestions.

 

[jpena418]

Thank you. One last question does your code look for No Call in any cell B210 through B260 or does it have to be in all cells. I need it to copy the set of cells if No Call is in any of those cells. thank you for your help thus far.

 

[jpena418]

JLGWhiz, I think I figured out why mine is not pulling any thing in. In the B210:B260 range the cells contain No Call xxxxx So they have other characters in the cell. How can i update it to search for any cells that contain No Call and not specifically No Call

 

[JLGWhiz]

See if this will work
Change this line

If Application.CountIf(ws.Range("B210:B260"), "No Call") > 0 Then

To this

If Application.CountIf(ws.Range("B210:B260"), "No Call*") > 0 Then