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

jpena418

New Member
Joined
Jul 30, 2020
Messages
5
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 30, 2020
Messages
5
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Jul 30, 2020
Messages
5
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • folder.jpg
    folder.jpg
    111.1 KB · Views: 7
  • Annotation 2020-07-31 104018.png
    Annotation 2020-07-31 104018.png
    53.7 KB · Views: 7

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jul 30, 2020
Messages
5
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jul 30, 2020
Messages
5
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if this will work
Change this line
VBA Code:
If Application.CountIf(ws.Range("B210:B260"), "No Call") > 0 Then

To this

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

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top