Macro - Find words/terms in cells and paste to new sheet

[JoeDelcambre]

I have tried a few macros and they seem to work for the most part. I have attached a Mini Sheet (dummy sheet) to help illustrate.

My goal is to find the following terms: ("Windows 7", "windows 7", "Win 7", "win 7", "XP", "Win XP", "win xp", "windows XP"), then paste the complete rows in which those terms are located to a new sheet (including column titles). The issue I'm running into is some of the keywords I'm looking for are not the only words within a cell. For example, a cell may have "74BBN win 7 TTP77". My current macros are not locating and pasting those rows over to the new sheet, but only the cells with only the exact phrase "win 7" for example.

I decided to start from scratch. Any help is greatly appreciated.

macro tests2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	Must_Pay	Funded	Agency	Directorate_ID	Division_ID	Acq_Package	OS	Amount_Paid	Amount_Owed	Date_Updated	Start_Date	End_Date	Updated_By
2	ABC568	yes	yes	USMC	J6	IOD	USMC-Package 789	vista	$ 4,000.00	$ 10,000.00	12/25/2021	2/5/2020	2/1/2022	John Smith
3	SDJ4478	yes	no	USMC	J6	CSD	USMC-Package 789	XP	$ 2,000.00	$ 12,000.00	11/15/2021	2/5/2020	2/2/2022	Jane Doe
4	LIU9898	no	yes	USA	J3	CSD	USA-Package 3398J	TTP4 Windows 7 HWLM	$ 3,500.00	$ 6,500.00	3/2/2021	3/1/2021	2/3/2022	John Doe
5	HBR5454	yes	no	USA	J3	SDD	USA-Package 3398J	Win 10	$ 2,500.00	$ 7,500.00	6/15/2021	4/1/2021	2/4/2022	Jane Smith
6	NHRI2347	yes	yes	USN	J2	EOD	USN-Package 923	Windows 10	$ 1,200.00	$ 10,000.00	1/22/2022	5/5/2020	2/5/2022	Jane Smith
7	NCUR2458	no	yes	USCG	J6	CIO	USCG-Package 3287	Vista	$ 6,500.00	$ 8,000.00	1/6/2022	9/4/2020	2/6/2022	Jane Smith
8	LKJ2147	no	yes	USAF	J3	CSD	USAF-Package 776534	Win XP	$ 2,100.00	$ 3,500.00	7/25/2021	7/5/2021	2/7/2022	Jane Smith
9	POI6698	yes	no	USA	J4	IOD	USA-Package 3398J	win xp	$ 9,800.00	$ 12,000.00	9/2/2021	4/16/2020	2/8/2022	Jane Smith
10	ABCS987	no	no	USAF	J1	SDD	USAF-Package 776534	TTP4 Windows 7 HWLM	$ 7,400.00	$ 8,000.00	6/6/2021	3/1/2021	2/9/2022	Joe Doe
11	SMN3298	no	yes	USSF	S4	CSD	USSF-Package 2312	Windows 7	$ 6,500.00	$ 9,500.00	9/22/2021	7/5/2021	2/10/2022	Joe Doe
12	LED8852	no	no	USSF	S4	IOD	USSF-Package 2312	Windows 10	$ 3,500.00	$ 3,500.00	11/2/2021	6/7/2020	2/11/2022	John Smith
13	LMN326	yes	yes	USCG	S2	CSD	USCG-Package 3287	Windows XP	$ 2,100.00	$ 2,500.00	7/9/2021	3/1/2021	2/12/2022	John Doe
14	WDC589	no	yes	USN	S6	CIO	USN-Package 923	Win 7	$ 1,750.00	$ 8,700.00	2/1/2022	11/9/2020	2/13/2022	Jane Doe
15	VFD365	yes	yes	USN	S6	CIO	USN-Package 923	Win xp	$ 630.00	$ 6,500.00	3/6/2021	3/25/2020	2/14/2022	Jane Smith
16	CVF127	yes	yes	USMC	S3	CIO	USMC-Package 789	74BBN win 7 TTP77	$ 2,500.00	$ 3,500.00	12/1/2021	1/6/2021	2/15/2022	Jane Smith
17	CVRF9863	yes	no	USN	J2	SDD	USN-Package 923	windows 7	$ 7,800.00	$ 7,800.00	2/2/2022	9/7/2020	2/16/2022	Jane Smith
18	MMLK145	no	no	USMC	J4	IOD	USMC-Package 789	windows 10	$ 9,800.00	$ 9,800.00	3/8/2021	3/19/2021	2/17/2022	John Doe
19	EWD441	yes	yes	USAF	S4	CSD	USAF-Package 776534	windows XP	$ 1,200.00	$ 4,500.00	8/5/2021	7/7/2020	2/18/2022	John Doe
20	RRB2285	no	no	USAF	S5	CSD	USAF-Package 776534	Windows 7	$ 3,200.00	$ 4,500.00	9/5/2021	9/3/2020	2/19/2022	John Doe
21	ABC568	yes	orange	abc	J6	xyz	USMC-Package 789	vista	$ 4,000.00	$ 10,000.00	12/25/2021	2/5/2020	2/1/2022	John Smith
22	SDJ4478	yes	orange	USMC	J6	CSD	USMC-Package 789	XP	$ 2,000.00	$ 12,000.00	11/15/2021	2/5/2020	2/2/2022	Jane Doe
23	LIU9898	no	yes	USA	J3	xyz	USA-Package 3398J	Windows 7	$ 3,500.00	$ 6,500.00	3/2/2021	3/1/2021	2/3/2022	John Doe
24	HBR5454	yes	no	abc	J3	CSD	USA-Package 3398J	Win 10	$ 2,500.00	$ 7,500.00	6/15/2021	4/1/2021	2/4/2022	Jane Smith
25	NHRI2347	yes	banana	USN	J2	EOD	USN-Package 923	Windows 10	$ 1,200.00	$ 10,000.00	1/22/2022	5/5/2020	2/5/2022	Jane Smith
26	NCUR2458	no	yes	abc	J6	xyz	USCG-Package 3287	Vista	$ 6,500.00	$ 8,000.00	1/6/2022	9/4/2020	2/6/2022	Jane Smith
27	LKJ2147	no	yes	USAF	J3	CSD	USAF-Package 776534	Win XP	$ 2,100.00	$ 3,500.00	7/25/2021	7/5/2021	2/7/2022	Jane Smith
28	POI6698	yes	apple	USA	J4	IOD	USA-Package 3398J	win xp	$ 9,800.00	$ 12,000.00	9/2/2021	4/16/2020	2/8/2022	Jane Smith
29	ABCS987	no	no	abc	J1	SDD	USAF-Package 776534	Windows 7	$ 7,400.00	$ 8,000.00	6/6/2021	3/1/2021	2/9/2022	Joe Doe
30	SMN3298	no	yes	USSF	S4	CSD	USSF-Package 2312	Windows 7	$ 6,500.00	$ 9,500.00	9/22/2021	7/5/2021	2/10/2022	Joe Doe
31	LED8852	no	no	def	S4	xyz	USSF-Package 2312	Windows 10	$ 3,500.00	$ 3,500.00	11/2/2021	6/7/2020	2/11/2022	John Smith
32	LMN326	yes	apple	abc	S2	CSD	USCG-Package 3287	Windows XP	$ 2,100.00	$ 2,500.00	7/9/2021	3/1/2021	2/12/2022	John Doe
33	WDC589	no		USN	S6	def	USN-Package 923	74BBN Win 7 TTP77	$ 1,750.00	$ 8,700.00	2/1/2022	11/9/2020	2/13/2022	Jane Doe
34	VFD365	yes	yes	USN	S6	xyz	USN-Package 923	Win xp	$ 630.00	$ 6,500.00	3/6/2021	3/25/2020	2/14/2022	Jane Smith
35	CVF127	yes	apple	abc	S3	xyz	USMC-Package 789	win 7	$ 2,500.00	$ 3,500.00	12/1/2021	1/6/2021	2/15/2022	Jane Smith
36	CVRF9863	yes	no	USN	J2	SDD	USN-Package 923	windows 7	$ 7,800.00	$ 7,800.00	2/2/2022	9/7/2020	2/16/2022	Jane Smith
37	MMLK145	no	no	abc	J4	IOD	USMC-Package 789	windows 10	$ 9,800.00	$ 9,800.00	3/8/2021	3/19/2021	2/17/2022	John Doe
38	EWD441	yes	yes	USAF	S4	CSD	USAF-Package 776534	windows XP	$ 1,200.00	$ 4,500.00	8/5/2021	7/7/2020	2/18/2022	John Doe
39	RRB2285	no	no	def	S5	CSD	USAF-Package 776534	Windows 7	$ 3,200.00	$ 4,500.00	9/5/2021	9/3/2020	2/19/2022	John Doe
Sheet1

 

[Marc L]

​

Store the terms in a free column like T for example with the same column header then you can use an advanced filter.​

Another way is to hardcode an array for the terms then use a filter with this array as criteria …​

 

[Marc L]

According to your attachment and according to the Macro Recorder using a filter Excel basics a VBA demonstration for starters​

to paste to the data source worksheet module :​

Sub Demo1()
        Application.ScreenUpdating = False
        If Sheets.Count = Index Then Sheets.Add , Me Else Sheets(Index + 1).UsedRange.Clear
    With UsedRange
        .Columns(8).AutoFilter 1, [{"*Win* 7*","*XP*"}], 7
        .Copy Sheets(Index + 1).[A1]
        .AutoFilter
    End With
        Application.ScreenUpdating = True
End Sub

 

[JoeDelcambre]

I inputted some of that in my macro on the dummy worksheet I posted, and I got it to work. I applied that to the actual project I was working on and it is copying/pasting all 44,000 rows on the new sheet. No idea why.