Search multiple workbooks in a folder and subfolders for a specific string

[jskasango]

I need a VBA code to let me browse to a folder and Search for a specific string in multiple workbooks ((*.xl*) Sheet.Results)) in the folder and subfolders . If the specified string is found the code should copy the entire row into a new "Found" workbook including the headers from the first "Hit" only and any "new" headers in subsequent "finds" should be appended to the right of the "Found".
Any assistance will be highly appreciated. THANK YOU VERY MUCH IN ADVANCE.

 

[jskasango]

The new wb has headers? It sounds like there can be more than 1 find per column? U didn't clarify whether the search is for the header and what "containing" and "Beginning with" has to do with the search? Dave

Sorry about that, the find is in the data area but I don’t mind having a confirmation that the headers are audited eg if header 1 is in column D in the first find and column G in the next find (in the next WB searched), the output should be in the same column in the output.

 

[jskasango]

Dave- Sending a sample WB as soon as I get to my destination. -Kasango

 

[NdNoviceHlp]

Kasango.... so search for each header which may be found multiple times in each column. The same header can also be found in multiple wbs. Copy the entire row (columns 1 to last used column) and transpose it to a column (the same column for the same header) in a new wb. That's a fairly big ask. Posting a sample data wb would certainly be helpful. Dave

 

[jskasango]

about.... so search for each header which may be found multiple times in each column. The same header can also be found in multiple wbs. Copy the entire row (columns 1 to last used column) and transpose it to a column (the same column for the same header) in a new wb. That's a fairly big ask. Posting a sample data wb would certainly be helpful. Dave

Dave-Please give me abou two hours. I will do that. My objective is stack the found data.
Thanks for your patience-kasango

 

[jskasango]

Dave-Please give me abou two hours. I will do that. My objective is stack the found data.
Thanks for your patience-kasango

3WHYB-SXID21-5.xls
	B	C	D	E																								AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP		AR				AV	AW	AX	AY
1	Entry	StockID	Name	BreedersPedigree1																								GrainYieldTons_FieldWt	RankNo	AnthesisDate	DaysToSilk	ASI	PlantHeightCm	EarHeightCm	EarPosition	RootLodgingPer	StemLodgingPer	EPPNo	BadHuskCoverPer	EarRotTotalPer	GrayLeafSpot1_5		LeafBlightTurcicum1_5				GrainMoisturePer	PlantStand_NumPerPlot	VirusMaizeStreak1_5	EarAspect1_5
2	ENT	STKID	CODE	PED2																								GYF	Rk	AD	DS	ASI	PH	EH	EPO	RL	SL	EPP	HC	ER	GLS		ET				MOI	NP	MSV	EA
3	NumberFormat																											0.00	0	0.0	0.0	0.0	0.0	0.0	0.00	0.0	0.0	0.00	0.0	0.0	0.0		0.0				0.0	0.0	0.0	0.0
4	Count																											90	90	90	90	90	90	90	90	90	90	90	90	90	90		90				90	90	90	90
5	TrialMean																											7.65	46	68.3	68.9	0.8	237.2	131.7	0.56	6.0	15.5	1.03	3.1	1.0	1.5		2.1				26.6	17.9	0.2	2.8
20	Entry	Stock	Name	Pedigree																										Anth	Days to	ASI	Plant	Ear	Ear	Lodging		Ears/	Husk	Ear	GLS		E.turc				Grain	Num	MSV	Ear
21		ID																										FW	Rank	Date	Silk		Height	Height	Position	Root	Stem	Plant	Cover	Rot							Moist	Plants		Aspect
22
23																												t/ha	Rank	d	d	d	cm	cm	0-1	%	%	#	%	%	1-5		1-5				%	#	1-5	1-5
24
25	1	D1731-1	CKH212481	CML566/DL171945//DL176972																								6.74	67	69	71	1	266	151	1	15	36	1.1	1	-3	1.5		2.3				25.1	16.2	0.0	2.9
26	2	D1731-2	CKH212482	CML566/DL171945//CML312																								5.32	88	70	70	1	240	132	1	-1	2	1.1	-9	-10	1.7		2.1				31.0	11.3	0.0	3.5
27	3	D1731-3	CKH212483	CML566/DL171945//DL171706																								7.95	36	67	66	(1)	261	143	1	11	43	1.1	0	0	1.7		2.3				28.3	18.1	0.0	2.2
28	4	D1731-4	CKH212484	CML566/DL171945//DL12416																								5.92	82	68	69	1	221	127	1	4	49	0.9	4	2	1.7		2.5				27.9	18.0	0.0	2.9
29	5	D1731-5	CKH212485	CML566/DL171945//DL172723																								8.26	28	67	67	(0)	226	128	1	1	5	1.2	-5	-4	1.7		2.3				33.5	17.8	0.0	2.7
30	6	D1731-6	CKH212486	CML566/DL171945//DL14387																								9.92	4	67	67	0	241	143	1	9	25	0.9	-8	-5	2.2		2.1				21.1	19.4	0.0	2.7
31	7	D1731-7	CKH212487	CML566/DL171945//DL172690																								6.96	62	70	72	2	248	147	1	2	7	1.1	18	-7	1.4		2.1				27.9	17.5	0.5	2.9
32	8	D1731-8	CKH212488	CML566/DL171945//CML536																								8.37	26	71	71	(0)	252	146	1	3	3	1.0	-5	4	1.2		1.5				28.0	19.4	0.0	2.5
33	9	D1731-9	CKH212489	CML566/DL171945//CZL1135																								6.05	81	66	65	(0)	230	128	1	13	25	0.9	8	7	1.0		2.3				22.1	18.0	0.0	3.5
34	10	D1731-10	CKH212490	CML566/DL171945//CZL15037																								10.91	2	67	67	(0)	256	126	1	8	13	1.1	-5	-3	1.7		2.0				25.9	20.1	1.5	2.3
35	11	D1731-11	CKH212491	CML566/DL171945//CL147165																								7.76	42	69	69	0	232	118	1	2	7	1.0	-3	-4	1.5		1.8				31.3	18.7	0.0	2.6
36	12	D1731-12	CKH212492	CML566/DL171945//DL177019																								9.23	10	69	69	0	241	133	1	1	0	1.1	-4	-4	1.2		2.5				29.7	19.2	0.0	2.9
37	13	D1731-13	CKH212493	CML444/CKL14549//DL176972																								7.94	37	69	71	2	260	159	1	14	25	1.0	-4	-5	1.7		2.3				26.3	18.0	0.0	2.3
38	14	D1731-14	CKH212494	CML444/CKL14549//CML312																								8.59	19	69	69	1	240	144	1	3	37	1.1	-8	0	1.5		2.1				28.8	11.2	0.0	3.5
39	15	D1731-15	CKH212495	CML444/CKL14549//DL171706																								6.89	65	69	70	1	227	129	1	2	17	0.9	20	4	1.5		2.1				25.6	16.7	0.5	3.0
40	16	D1731-16	CKH212496	CML444/CKL14549//DL12416																								7.20	57	68	69	1	214	123	1	-1	27	1.0	-3	-6	1.7		2.3				28.5	19.2	2.0	2.8
41	17	D1731-17	CKH212497	CML444/CKL14549//DL172723																								7.92	39	68	68	(0)	229	125	1	7	15	1.0	-4	10	1.5		2.3				26.8	20.0	0.0	2.5
42	18	D1731-18	CKH212498	CML444/CKL14549//DL14387																								9.11	14	69	70	2	232	136	1	5	16	1.1	-6	-7	2.0		2.5				25.1	19.7	0.5	2.5
43	19	D1731-19	CKH212499	CML444/CKL14549//DL172690																								6.13	79	70	71	1	247	161	1	2	14	1.0	5	10	1.5		2.0				28.4	19.7	0.0	2.7
44	20	D1731-20	CKH212500	CML444/CKL14549//CML536																								9.54	8	69	68	(0)	262	145	1	5	4	1.0	-6	-6	1.2		1.8				31.5	20.0	0.5	2.3
Results

Cell Formulas
Range	Formula
AC4:AP4,AR4,AV4:AY4	AC4	=COUNT(AC25:AC114)
AC5:AP5,AR5,AV5:AY5	AC5	=AVERAGE(AC25:AC114)

Cells with Data Validation
Cell	Allow	Criteria
AA1:AY1	List	=rTrial

 

[jskasango]

David, please let me know if it is now clear about the headers.
The data range to search for the string is everything from row 25 and it may also be sitting in hidden columns D and E.
Thanks a lot.

 

[NdNoviceHlp]

???? I don't see the headers corresponding to anything? Dave

 

[jskasango]

???? I don't see the headers corresponding to anything? Dave

The headers are column labels for the data that we are searching from row25 down. When we get a hit copy the entire row to the destination WB, the information will be under those labels. As the stack grows, the a hit may contain a piece of data that has a label that has never been used, therefore we would need this new label to be introduced at row1 far right.

Or simply put, let’s get all hits from row25 in all worksheets named “Results” placed in a new WB using entire row copy paste to the output.

 

[NdNoviceHlp]

Headers like "entry", "stock ID", "Name", etc? Those terms don't show up anywhere in the corresponding column rows 25 down? Dave

 

[jskasango]

Headers like "entry", "stock ID", "Name", etc? Those terms don't show up anywhere in the corresponding column rows 25 down? Dave

Dave- Now I get you! Ignore everything from row2 to row 24. Those rows are used for other purposes. My interest is row1 then every data from row 25 downwards.