Excel loop from text file.. paste results in new spreadsheet

[Bad husband]

I have an Excel table with over 2000 records.

I have a text table with matching item codes that i use to lookup the values from spreadsheet. It might have only a few matching records in the spreadsheet. (eg 31901 & 31903)

I want it to loop thru the excel spreadsheet and only copy the rows to a new table. Basically i want to create a count sheet from the item codes that are in the text file.

A B C D E F
Item code - Loc - Lot - Stat - Qty - Ord
31900 - 33 - kj - A - 410 - 25
31901 - 34 - mt - A - 700 - 5
31902 - 35 - zs - A - 600 - 10
31903 - 36 - we - Q - 800 - 45
31904 - 37 - jh - A - 675 - 30

I have found some code but is not complete. I can see that it is finding the records but stuck on how to copy rows to new spreadsheet.

Code: Select all

List := "list.txt", FilePath := "Inventory Cycle-Count.xlsx"
fileread, list, %list%
StringReplace, list,list,`r`n,`,, All
Xl := ComObjCreate("Excel.Application")	;create a handle
Xl.Workbooks.Open(a_scriptdir "\" FilePath,0,0) ;open xl file invisible
lstrw := xl.Sheets(1).UsedRange.rows.count	;find last row of used range
xl.Sheets(1).range("D34:R"lstrw).copy	;copy first 2 columns.
loop, parse, clipboard,`n,`r									
	{
	RC%a_index% := StrSplit(a_loopfield, A_Tab) ;...and put in array
	 item := RC%a_index%.1									
	if item in %list%		;if one of the items matches the list			
		BList .=  RC%a_index%.1 "`n"	;save it to Blist var			       
	}
sort, Blist	;sort the list (optional)
MsgBox, %Blist%			;show result
Clipboard := Blist "`r" Blist1

thanks for looking and helping