Access Multiple Files in a Folder and Perform Autofilter, Copy and Paste into another Workbook

[ragav_in]

Hi team,

I am currently working on a testing project which needs to access multiple Test Result files and extract the Defects from them and put them into one Consolidated Sheet. With my limited knowledge in VBA and using the record macro feature, I was able to write this code that will do the following.

1. Select the sheet named "MySheet"
2. Perform Autofilter and Filter out values that are "FAIL" in Column M
3. Copies the entire range of Column A to Column N (including Header Row) and paste it into the sheet named "Defect"
4. Then it selects the next Sheet named "YourSheet"
5. Performs Autofilter and Filter out values that are "FAIL" in Column M
6. This time, it copies all the rows (except the Header Row), goes to the sheet named "Defect", finds the existing last row, and then pastes the content after that row

Sub Ultim()
'
' Ultim Macro
'This macro will filter and copies all the FAIL records to the Data Defect Log


   Sheets("MySheet").Select
'    Sheets(1).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Range("M1").Select
    ActiveSheet.Range("$A:$N").AutoFilter Field:=13, Criteria1:="FAIL"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Defect").Select
    Range("A1").Select
    ActiveSheet.Paste
   
    Sheets("YourSheet").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    Range("M1").Select
'    ActiveSheet.Range("$A$1:$N$110").AutoFilter Field:=13, Criteria1:="FAIL"
    ActiveSheet.Range("$A:$N").AutoFilter Field:=13, Criteria1:="FAIL"

'   This line is to select only the values and not header again
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Sheets("Defect").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    'This line is for going to the last empty row in the existing Defect Sheet for pasting
    ActiveCell.Offset(1, 0).Select

    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Range("A1").Select
    Selection.End(xlToRight).Select
    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("N20").Select
    ActiveWorkbook.Save
End Sub

The above macro has some hard-coded values such as sheet name and also this works only when both the sheets (MySheet and YourSheet) are available in the same workbook in which the Macro is executed. However, my requirement is different as below.

1. There will be a folder that will contain multiple individual workbooks.
2. All workbooks will have only 1 sheet of the same format similar to MySheet or YourSheet, but the names are dynamic and unknown
3. The "new" macro to be written should ask for the location of the folder where all Individual workbooks are available
4. Once the path is chosen, the Macro should execute in each Individual workbook; i.e., Open each one of them, perform the Autofilter and Copy from the individual sheets, and paste them in another Workbook (which will have a sheet named Defect), and paste the data there
5. Note that only the first time (first workbook), the Copy Paste should happen with the Header Rows.
6. From the 2nd workbook onwards, the macro needs to copy-paste the rows excluding the Header Rows (as they are already present in the "Defect" sheet)
7. The Macro should save the new workbook in the same folder as the Individual Files are available and provide some name (that can be customizable)

I have attached the "Defect Log.xlsm" with this post in which the current macro is available. Also, I have attached 2 individual workbooks that have sheets with the data in which the activity needs to be performed.

I would like to humbly request this community to help me with the above requirement. To date, we have many individual files that need to be accessed and data to be copy-pasted manually. If we could have the macro perform this task, there will be a reduction in human effort and also an increase in the correctness of the defect consolidation. I understand that this will be a little complicated and needs some effort to be put, and I would thank each and every individual for their time and effort.

Defect Log.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	ProcessArea	DataObject	Target	Source	ABCD	FIELDNAME	Critical	Risk	Impact	Pre_Value	Post_Value	Result	Count
2	Project	ProcessArea	DataObject	Target	Source	ABCD	FIELDNAME	Critical	Risk	Impact	Pre_Value	Post_Value	Result	Count
3	Project1	People	Object 1	Target 1	File	XYZ530	Field1	Not Critical	High				PASS	464
4	Project1	People	Object 2	Target 2	File	XYZ530	Field2	Not Critical	High			0	FAIL	464
5	Project1	People	Object 3	Target 3	File	XYZ530	Field2	Not Critical	High			0	FAIL	464
6	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High				FAIL	464
7	Project1	People	Object 5	Target 5	File	XYZ530	Field4	Critical	High	Key		ST	FAIL	5
8	Project1	People	Object 6	Target 6	File	XYZ530	Field4	Critical	High	Key	PCE	ST	FAIL	459
9	Project1	People	Object 7	Target 7	File	XYZ530	Field4	Critical	High	Key			PASS	464
10	Project1	People	Object 8	Target 8	File	XYZ530	Field4	Critical	High	Key		0.000	FAIL	292
11	Project1	People	Object 9	Target 9	File	XYZ530	Field4	Critical	High	Key	10080.000	10080.000	PASS	6
12	Project1	People	Object 10	Target 10	File	XYZ530	Field4	Critical	High	Key	1010.000	1010.000	PASS	2
13	Project1	People	Object 11	Target 11	File	XYZ530	Field4	Critical	High	Key	1020.000	1020.000	PASS	1
14	Project1	People	Object 12	Target 12	File	XYZ530	Field4	Critical	High	Key	10200.000	10200.000	PASS	4
15	Project1	People	Object 13	Target 13	File	XYZ530	Field4	Critical	High	Key	10500.000	10500.000	PASS	1
MySheet

Defect Log.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	ProcessArea	DataObject	Target	Source	ABCD	FIELDNAME	Critical	Risk	Impact	Pre_Value	Post_Value	Result	Count
2	Project	ProcessArea	DataObject	Target	Source	ABCD	FIELDNAME	Critical	Risk	Impact	Pre_Value	Post_Value	Result	Count
3	Project1	Tools	Data Object 1	Target 1	File	XYZ530	Field5	Not Critical	High				PASS	464
4	Project1	Tools	Data Object 2	Target 2	File	XYZ530	Field6	Not Critical	High				PASS	464
5	Project1	Tools	Data Object 3	Target 3	File	XYZ530	Field6	Not Critical	High			0	FAIL	464
6	Project1	Tools	Data Object 4	Target 4	File	XYZ530	Field7	Not Critical	High				FAIL	464
7	Project1	Tools	Data Object 5	Target 5	File	XYZ530	Field8	Critical	High	Key		ST	FAIL	5
8	Project1	Tools	Data Object 6	Target 6	File	XYZ530	Field8	Critical	High	Key	PCE	ST	FAIL	459
9	Project1	Tools	Data Object 7	Target 7	File	XYZ530	Field8	Critical	High	Key			PASS	464
10	Project1	Tools	Data Object 8	Target 8	File	XYZ530	Field8	Critical	High	Key		0.000	FAIL	292
11	Project1	Tools	Data Object 9	Target 9	File	XYZ530	Field8	Critical	High	Key	10080.000	10080.000	PASS	6
12	Project1	Tools	Data Object 10	Target 10	File	XYZ530	Field8	Critical	High	Key	1010.000	1010.000	PASS	2
YourSheet

Defect Log.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Project	ProcessArea	DataObject	Target	Source	ABCD	FIELDNAME	Critical	Risk	Impact	Pre_Value	Post_Value	Result	Count
2	Project1	People	Object 2	Target 2	File	XYZ530	Field2	Not Critical	High			0	FAIL	464
3	Project1	People	Object 3	Target 3	File	XYZ530	Field2	Not Critical	High			0	FAIL	464
4	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High				FAIL	464
5	Project1	People	Object 5	Target 5	File	XYZ530	Field4	Critical	High	Key		ST	FAIL	5
6	Project1	People	Object 6	Target 6	File	XYZ530	Field4	Critical	High	Key	PCE	ST	FAIL	459
7	Project1	People	Object 8	Target 8	File	XYZ530	Field4	Critical	High	Key		0.000	FAIL	292
8	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High		1280.000	1280.000	FAIL	2
9	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High		1851.000	1851.000	FAIL	1
10	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High		272.000	272.000	FAIL	1
11	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High		3886.000	3886.000	FAIL	1
12	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High		5760.000	5760.000	FAIL	2
13	Project1	People	Object 4	Target 4	File	XYZ530	Field3	Not Critical	High		838.000	838.000	FAIL	1
14	Project1	People	Object 11	Target 11	File	XYZ530	Field4	Critical	High	Key		0.000	FAIL	464
15	Project1	Tools	Data Object 3	Target 3	File	XYZ530	Field6	Not Critical	High			0	FAIL	464
16	Project1	Tools	Data Object 4	Target 4	File	XYZ530	Field7	Not Critical	High				FAIL	464
17	Project1	Tools	Data Object 5	Target 5	File	XYZ530	Field8	Critical	High	Key		ST	FAIL	5
18	Project1	Tools	Data Object 6	Target 6	File	XYZ530	Field8	Critical	High	Key	PCE	ST	FAIL	459
19	Project1	Tools	Data Object 8	Target 8	File	XYZ530	Field8	Critical	High	Key		0.000	FAIL	292
20	Project1	Tools	Data Object 11	Target 11	File	XYZ530	Field8	Critical	High	Key		0.000	FAIL	464
Defect

 

[Alex Blakenburg]

Why not just use Power Query ?

 

[ragav_in]

Dear Alex, thanks for your time. I do not have knowledge on using Power Query till date. Also I am not sure of how it will be different from VBA Macro; hence wanted to have a VBA code written which can be re-used across my organization.