In a bit of help with automating an report

[JimRon]

I am trying to pull data from an pre complied data dump file. Starting with A10 until three blank rows are detected from the user defined source file. The data is in columns A:AU but only need certain data from within different cells. Need to loop thru the source report to pull from until three blank rows are detected from the user defined source file. I think that I am on the right track in getting this done but not 100% sure if it will work as expected. What needs to occur ( I hope) is to have data pulled based on Product value & placed in a specific format for an report. I have included some of the code and welcome any advise on improving it and getting the expected results.

' Allow the user to define the filename to be opened

Sub Get_Data_From_File()

' Defines the varibles used

Dim FileToOpen As Variant
Dim OpenBook As Workbook

' Pervent Screen Updating

Application.ScreenUpdating = False

' Request the file to open from the user.

FileToOpen = Application.GetOpenFilenmae(Title:="Browse for your file & Import Range", FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen <> False Then
  Set OpenBook = Application.Workbooks.Open(FileToOpen)
  OpenBook.Sheets(1).Range("A10:AU") 
 'Not sure if this is needed . . . ThisWorkbook.Workseets("Line 4 Report").Range("A2").PasteSpecial xlPasteValues
' Don't want to change scource file . . . OpenBook.Close False
End If

' Allows Screen updating to occur.

Application.ScreenUpdating = True


End Sub

' pull data starting with A10 until three blank rows are detected from the user defined source file.
' Data is in columns A:AU but only need certain data from different cells
' Source data is in a pre-complied format and needs to have data pulled based on Product value & placed
' in a specific format for line report.

Sub PullData()

' Defines Variables to be used

Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Line 4 Report")

' Searches for the next blank row for data entry
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

With ws

    If FileToOpen.Cells(iRow, 8).Value = "Product1" Then
        .Cells(iRow, 1).Value = FileToOpen.Cells(iRow, 1).Value
        .Cells(iRow, 2).Value = FileToOpen.Cells(iRow, 2).Value
        .Cells(iRow, 3).Value = FileToOpen.Cells(iRow, 8).Value
        .Cells(iRow, 4).Value = FileToOpen.Cells(iRow, 9).Value
        .Cells(iRow, 5).Value = FileToOpen.Cells(iRow, 4).Value
        .Cells(iRow, 6).Value = FileToOpen.Cells(iRow, 14).Value
        .Cells(iRow, 7).Value = FileToOpen.Cells(iRow, 18).Value
        .Cells(iRow, 8).Value = FileToOpen.Cells(iRow, 20).Value
        .Cells(iRow, 9).Value = FileToOpen.Cells(iRow, 21).Value
        .Cells(iRow, 10).Value = FileToOpen.Cells(iRow, 22).Value
        .Cells(iRow, 11).Value = FileToOpen.Cells(iRow, 23).Value
        .Cells(iRow, 12).Value = FileToOpen.Cells(iRow, 50).Value
        .Cells(iRow, 13).Value = FileToOpen.Cells(iRow, 30).Value
        .Cells(iRow, 14).Value = FileToOpen.Cells(iRow, 28).Value
        .Cells(iRow, 15).Value = FileToOpen.Cells(iRow, 29).Value
        .Cells(iRow, 16).Value = FileToOpen.Cells(iRow, 31).Value
        .Cells(iRow, 17).Value = FileToOpen.Cells(iRow, 32).Value
        .Cells(iRow, 18).Value = FileToOpen.Cells(iRow, 36).Value
        .Cells(iRow, 19).Value = FileToOpen.Cells(iRow, 41).Value
        .Cells(iRow, 20).Value = FileToOpen.Cells(iRow, 19).Value
        .Cells(iRow, 21).Value = FileToOpen.Cells(iRow, 44).Value
        .Cells(iRow, 22).Value = FileToOpen.Cells(iRow, 45).Value
        .Cells(iRow, 23).Value = FileToOpen.Cells(iRow, 47).Value
        .Cells(iRow, 24).Value = FileToOpen.Cells(iRow, 46).Value
        .Cells(iRow, 25).Value = FileToOpen.Cells(iRow, 48).Value
    ElseIf FileToOpen.Cells(iRow, 8).Value = "Product2" Then
        .Cells(iRow, 1).Value = FileToOpen.Cells(iRow, 1).Value
        .Cells(iRow, 2).Value = FileToOpen.Cells(iRow, 2).Value
        .Cells(iRow, 3).Value = FileToOpen.Cells(iRow, 8).Value
        .Cells(iRow, 4).Value = FileToOpen.Cells(iRow, 9).Value
        .Cells(iRow, 5).Value = FileToOpen.Cells(iRow, 4).Value
        .Cells(iRow, 6).Value = FileToOpen.Cells(iRow, 14).Value
        .Cells(iRow, 7).Value = FileToOpen.Cells(iRow, 18).Value
        .Cells(iRow, 8).Value = FileToOpen.Cells(iRow, 20).Value
        .Cells(iRow, 9).Value = FileToOpen.Cells(iRow, 21).Value
        .Cells(iRow, 10).Value = FileToOpen.Cells(iRow, 22).Value
        .Cells(iRow, 11).Value = FileToOpen.Cells(iRow, 23).Value
        .Cells(iRow, 12).Value = FileToOpen.Cells(iRow, 50).Value
        .Cells(iRow, 13).Value = FileToOpen.Cells(iRow, 30).Value
        .Cells(iRow, 14).Value = FileToOpen.Cells(iRow, 28).Value
        .Cells(iRow, 15).Value = FileToOpen.Cells(iRow, 29).Value
        .Cells(iRow, 16).Value = FileToOpen.Cells(iRow, 31).Value
        .Cells(iRow, 17).Value = FileToOpen.Cells(iRow, 32).Value
        .Cells(iRow, 18).Value = FileToOpen.Cells(iRow, 36).Value
        .Cells(iRow, 19).Value = FileToOpen.Cells(iRow, 41).Value
        .Cells(iRow, 20).Value = FileToOpen.Cells(iRow, 19).Value
        .Cells(iRow, 21).Value = FileToOpen.Cells(iRow, 44).Value
        .Cells(iRow, 22).Value = FileToOpen.Cells(iRow, 45).Value
        .Cells(iRow, 23).Value = FileToOpen.Cells(iRow, 47).Value
        .Cells(iRow, 24).Value = FileToOpen.Cells(iRow, 46).Value
        .Cells(iRow, 25).Value = FileToOpen.Cells(iRow, 48).Value
 
 ' . . . continues for a total of eight products
 
    End If
    
            
End With

End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your "Line 4 Report" sheet and the also of the source sheet. Alternately, you could upload a copy of your destination file and source file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[JimRon]

Ok. First the source file (This just small amount of the file has it has around 3569 rows of data)

Repair Order - Raw Data Dump.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
9	Repair Order Number	RMA Number	External RMA Number	Serial#	Reported Serial#	Product Type	Manufacturer	Product	Product Part#	Reported Part#	Manufacturer Part#	Part Description	Service Type	Status	Inspection Type	Inspection Code	Inspection Group	Inspection Name	Inspection Completed By	Defect Location	Defect Name	Consumed Product Part#	Consumed Item Name	Consumed Item Type	Quantity	Return Reason	Disposition	Received Date	Repair Date	Repair Completed By	Shipped Date	Closed Date	Project	Company	Customer Name	Airport Location Code	Address	City	State	Zip	Country	Phone	Company Name	Inbound Tracking Number	Outbound Tracking Number	Cycle Time	Bouncer	Hold Parts	In-Warranty	Repair Action
10	RO2020101800004	R2020101800004	RITM2146593	R00025806	N/A	Kiosk	Electronics for Imaging, Inc.	G5 Card Reader	890211	N/A	45104747	Mininet5; MininetM500 Station	Advanced Exchange	Awaiting Inspection	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	0	Display problem	Repair	05/26/2021	N/A	N/A	N/A	N/A	Kiosk	Product Office	TaylerWalker	WTCKN	10 UNION SQUARE EAST	NEW YORK	New York	10003	United States of America	2123533860	Kiosk	397964875750	397964875224	1	No	0	0	N/A
11	RO2020112700157	R2020112700156	R2020112700156	BAH05002	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 2	422040013	N/A	N/A	PRINTER; ASTRA 2 (N VERSION BT MODULE) VERSION 2.1 + EDR	Repair & Return to Customer	Delivered	N/A	N/A	N/A	N/A	Roberto Martinez (OSV)	Main Housing	Damaged	714115	Cover Open Switch (with Harness)	Part	1	No RMA	Repair	11/27/2020	05/25/2021	Diosdado Bullen	05/25/2021	05/26/2021	GOTS - Printers	Product Express	randypuckett	EQUAD	2903 SPRANKLE/RANDY PUCKETT	MEMPHIS	Tennessee	38118	United States of America	9013605060	GOTS - Printers	111111111111	787582050157	0	Yes	0	0	Replaced
12	RO2020112700157	R2020112700156	R2020112700156	BAH05002	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 2	422040013	N/A	N/A	PRINTER; ASTRA 2 (N VERSION BT MODULE) VERSION 2.1 + EDR	Repair & Return to Customer	Delivered	N/A	N/A	N/A	N/A	Roberto Martinez (OSV)	Main Housing	Damaged	714119	LCD Unit VLSG0620-01	Part	1	No RMA	Repair	11/27/2020	05/25/2021	Diosdado Bullen	05/25/2021	05/26/2021	GOTS - Printers	Product Express	randypuckett	EQUAD	2903 SPRANKLE/RANDY PUCKETT	MEMPHIS	Tennessee	38118	United States of America	9013605060	GOTS - Printers	111111111111	787582050157	0	Yes	0	0	Replaced
13	RO2020112700157	R2020112700156	R2020112700156	BAH05002	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 2	422040013	N/A	N/A	PRINTER; ASTRA 2 (N VERSION BT MODULE) VERSION 2.1 + EDR	Repair & Return to Customer	Delivered	N/A	N/A	N/A	N/A	Roberto Martinez (OSV)	Main Housing	Damaged	714881	Peel off Sensor Cover	Part	1	No RMA	Repair	11/27/2020	05/25/2021	Diosdado Bullen	05/25/2021	05/26/2021	GOTS - Printers	Product Express	randypuckett	EQUAD	2903 SPRANKLE/RANDY PUCKETT	MEMPHIS	Tennessee	38118	United States of America	9013605060	GOTS - Printers	111111111111	787582050157	0	Yes	0	0	Replaced
14	RO2021012500166	R2021012500185	R2021012500185	JAB8150171	N/A	Handhelds	Zebra Technologies	WT6000	607753	N/A	WT60A0-TS0LEO1	WRIST TERMINAL; WT6000 FXG	Repair & Return to Customer	Awaiting Packaging	DT	DT0013	Vibrator	Functional	Dallas Mize (OSV)	Mainboard	Damaged	717268	FRU:MAIN BOARD,WT6000	Part	1	Others	Repair	05/25/2021	05/26/2021	Dallas Mize (OSV)	N/A	N/A	OT - Operations Technology FXG	Product Ground	FDX GRD/GRAND RAPIDSCHARLES VANDERZWAAG	N/A	3466 SHIPPERS DRIVE NW	GRAND RAPIDS	Michigan	49544	United States of America	6166471900		N/A	N/A	1	No	0	0	Replaced
15	RO2021012500166	R2021012500185	R2021012500185	JAB8150171	N/A	Handhelds	Zebra Technologies	WT6000	607753	N/A	WT60A0-TS0LEO1	WRIST TERMINAL; WT6000 FXG	Repair & Return to Customer	Awaiting Packaging	VI	VI004	N/A	Outer Casing	Dallas Mize (OSV)	Top Housing	Damaged	715407	SCAN CONNECTOR; WT6	Part	2	Others	Repair	05/25/2021	05/26/2021	Dallas Mize (OSV)	N/A	N/A	OT - Operations Technology FXG	Product Ground	FDX GRD/GRAND RAPIDSCHARLES VANDERZWAAG	N/A	3466 SHIPPERS DRIVE NW	GRAND RAPIDS	Michigan	49544	United States of America	6166471900		N/A	N/A	1	No	0	0	Replaced
16	RO2021020800715	R2021020801093	RITM2414348	75851	N/A	Dropbox Equipment	Bellatrix	IR Lock	795433	N/A	070-0102-16	IR LOCK; DROPBOX; LOCK ONLY; (W / BATTS) MFGR #070-0102-17	Repair & Return to Inventory	Closed	N/A	N/A	N/A	N/A	Molito Mopal (OSV)	N/A	N/A	N/A	N/A	N/A	0	Others	Repair	02/11/2021	N/A	N/A	N/A	05/26/2021	GOTS - Dropbox	Product Express	GaryRedshaw	69356	3100 HANSEN WAY	PALO ALTO	California	94304	United States of America	8883398324	GOTS - Dropbox	118149053425	N/A	1	No	0	0	N/A
17	RO2021021100574	R2021021100747	RITM2427009	71355	N/A	Dropbox Equipment	Bellatrix	IR Lock	795433	N/A	070-0102-16	IR LOCK; DROPBOX; LOCK ONLY; (W / BATTS) MFGR #070-0102-17	Repair & Return to Inventory	Closed	N/A	N/A	N/A	N/A	Molito Mopal (OSV)	N/A	N/A	N/A	N/A	N/A	0	Others	Repair	02/23/2021	N/A	N/A	N/A	05/26/2021	GOTS - Dropbox	Product Express	GaryRedshaw	95793	55 ALMADEN BLVD	SAN JOSE	California	95113	United States of America	8883398324	GOTS - Dropbox	118149053609	N/A	1	No	0	0	N/A
18	RO2021021100695	R2021021100922	RITM2427517	VM3W172566080	N/A	Computer, Industrial	Honeywell International	VM3 Thor	195900024	N/A	VM3W2F1A1AUS1ED	FLAT PANEL; FXF HONEYWELL VM3 THOR; WIN10ML; 64G INDOOR USA	Repair & Return to Customer	In Transit	N/A	N/A	N/A	N/A	Thea Xayaphet	Software/Firmware	Software Issue	000003	Operating System	Part	1	Won’t turn on/off	Repair	05/18/2021	05/25/2021	Andrew Chaney	05/25/2021	05/25/2021	OT - Operations Technology	Product Freight	KevinBair	FFBGM	175 BUCK RD	NICHOLS	New York	13812	United States of America	9516817400	OT - Operations Technology	N/A	787586706453	1	No	0	0	Replaced
19	RO2021022300415	R2021022300523	R2021022300523	BDA15030	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 3	422040016	N/A	B-FP3D-GH30-FE-R(AD)	Printer; Astra 3 inch; Domestic; B-FP3D-GH32-AD-R-AD	Repair & Return to Customer	Delivered	N/A	N/A	N/A	N/A	Riean Onorati (OSV)	Main Housing	Damaged	715933	Func Unit Assy-FE-FP3D (ROHS)	Part	1	Damaged	Repair	02/23/2021	05/21/2021	Jacob Resendez (OSV)	05/24/2021	05/26/2021	GOTS - Printers	Product Express	RYANRUBLE	SGFA	2221 EAST OLIVE COURT	SPRINGFIELD	Missouri	65802	United States of America	4178688371	GOTS - Printers	772922027368	787516683945	1	No	0	0	Replaced
20	RO2021030100381	R2021030100584	R2021030100584	B5G9M53	N/A	Computers, Notebooks	Various	Generic Notebook	506	N/A	GENERICLAPTOP	NOTEBOOK; CORP PC FXS GENERIC FEC #	Repair & Return to Customer	Delivered	DT	DT0040	Power On / Burn In	Locks Up	James Jarrett	Mainboard	Inoperative	711627	SYSTEM BOARD – LT	Part	1	No RMA	Repair	03/01/2021	05/25/2021	Yaaqob Almurshedi	05/25/2021	05/26/2021	Corporate Equipment Repair (CER)	Product Services	MattBindbeutel	N/A	3650 Hacks Cross Rd Building E 2nd Floor	MEMPHIS	Tennessee	38125	United States of America	9014344651	Corporate Equipment Repair (CER)	485210576851	787586831424	0	No	0	0	Replaced
21	RO2021030100381	R2021030100584	R2021030100584	B5G9M53	N/A	Computers, Notebooks	Various	Generic Notebook	506	N/A	GENERICLAPTOP	NOTEBOOK; CORP PC FXS GENERIC FEC #	Repair & Return to Customer	Delivered	N/A	N/A	N/A	N/A	James Jarrett	Software/Firmware	Preventive Maintenance	000003	Operating System	Part	1	No RMA	Repair	03/01/2021	05/25/2021	Yaaqob Almurshedi	05/25/2021	05/26/2021	Corporate Equipment Repair (CER)	Product Services	MattBindbeutel	N/A	3650 Hacks Cross Rd Building E 2nd Floor	MEMPHIS	Tennessee	38125	United States of America	9014344651	Corporate Equipment Repair (CER)	485210576851	787586831424	0	No	0	0	Replaced
22	RO2021031500334	R2021031500717	RITM2514693	BDA21551	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 3	422040016	N/A	B-FP3D-GH30-FE-R(AD)	Printer; Astra 3 inch; Domestic; B-FP3D-GH32-AD-R-AD	Repair & Return to Customer	In Transit	N/A	N/A	N/A	N/A	Riean Onorati (OSV)	Main Housing	Damaged	715933	Func Unit Assy-FE-FP3D (ROHS)	Part	1	Others	Repair	03/16/2021	05/25/2021	Jacob Resendez (OSV)	05/25/2021	05/26/2021	GOTS - Printers	Product Express	RICHARDREINHARDT	HPNA	669 SOUTH THIRD AVENUE	MOUNT VERNON	New York	10550	United States of America	9146641704	GOTS - Printers	138843747010	787586903574	1	No	0	0	Replaced
23	RO2021031500334	R2021031500717	RITM2514693	BDA21551	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 3	422040016	N/A	B-FP3D-GH30-FE-R(AD)	Printer; Astra 3 inch; Domestic; B-FP3D-GH32-AD-R-AD	Repair & Return to Customer	In Transit	N/A	N/A	N/A	N/A	Riean Onorati (OSV)	Main Housing	Damaged	715934	Paper Cover-MO-FP3D (ROHS)	Part	1	Others	Repair	03/16/2021	05/25/2021	Jacob Resendez (OSV)	05/25/2021	05/26/2021	GOTS - Printers	Product Express	RICHARDREINHARDT	HPNA	669 SOUTH THIRD AVENUE	MOUNT VERNON	New York	10550	United States of America	9146641704	GOTS - Printers	138843747010	787586903574	1	No	0	0	Replaced
24	RO2021031800013	R2021031800024	RITM2524948	BAC43484	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 2	422040006	N/A	B-EP4DL-GH30-QM-R	PRINTER; ASTRA 2 PORTABLE LABEL; REPL FOR ASTRAPLUS	Repair & Return to Customer	Closed	N/A	N/A	N/A	N/A	Jerod Stone (OSV)	N/A	N/A	N/A	N/A	N/A	0	Others	Repair	03/19/2021	04/09/2021	Jerod Stone (OSV)	N/A	05/26/2021	GOTS - Printers	Product Express	RANDYPUCKETT	EQUAH	2903 SPRANKLE/RANDY PUCKETT	MEMPHIS	Tennessee	38118	United States of America	9017974933	GOTS - Printers	111	Picked Up	1	No	0	0	N/A
25	RO2021032500250	R2021032500434	R2021032500434	RGC39F0431	N/A	Handhelds	Getac Technology	F110-G2	939014492	N/A	52628707008N	LAPTOP; GETAC;F110G2/1HAF4/S6S2X2/AXXXFB/S1A11DA2F1/3XXXXX	Repair & Return to Customer	Closed	N/A	N/A	N/A	N/A	Kamaljit Kaur (OSV)	N/A	N/A	N/A	N/A	N/A	0	POWER	Repair	03/29/2021	N/A	N/A	N/A	05/26/2021	External Getac Repairs	Getac	RobertRowell	N/A	2975 Sprankle Dr	Memphis	Tennessee	38118	United States of America	9014829025	External Getac Repairs	785183058790	BER	1	No	0	0	N/A
26	RO2021032500252	R2021032500434	R2021032500434	RK203F1871	N/A	Handhelds	Getac Technology	F110-G3	939019524	N/A	5262878301JB	TABLET; GETAC;F110G3/1DBF3/S6S6X2/AXXXGB/S1A11DA2F1/3XX000/X	Repair & Return to Customer	Closed	N/A	N/A	N/A	N/A	Kamaljit Kaur (OSV)	Device	Damaged	581187830007	SERVICE PARTS;CASE B KIT W/I DIGI ENHANCED ANTI SHOCK F110G3	Part	1	Physical Damage	Repair	03/29/2021	03/31/2021	Mandy Williams	03/31/2021	05/26/2021	External Getac Repairs	Getac	RobertRowell	N/A	2975 Sprankle Dr	Memphis	Tennessee	38118	United States of America	9014829025	External Getac Repairs	785183058790	322773715903698	1	No	0	0	N/A
27	RO2021033000481	R2021033000739	RITM2557888	BAC43155	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 2	422040006	N/A	B-EP4DL-GH30-QM-R	PRINTER; ASTRA 2 PORTABLE LABEL; REPL FOR ASTRAPLUS	Repair & Return to Customer	In Transit	N/A	N/A	N/A	N/A	Riean Onorati (OSV)	Main Housing	Damaged	N/A	N/A	N/A	0	Damaged	Repair	03/31/2021	05/25/2021	Tui Mataele (OSV)	05/25/2021	05/25/2021	GOTS - Printers	Product Express	RonVan Overbeke	OAKH	1 SALLY RIDE WAY	OAKLAND	California	94621	United States of America	5106393700	GOTS - Printers	773303574085	787571615544	1	No	0	0	N/A
28	RO2021033000481	R2021033000739	RITM2557888	BAC43155	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 2	422040006	N/A	B-EP4DL-GH30-QM-R	PRINTER; ASTRA 2 PORTABLE LABEL; REPL FOR ASTRAPLUS	Repair & Return to Customer	In Transit	N/A	N/A	N/A	N/A	Riean Onorati (OSV)	Right Housing	Damaged	N/A	N/A	N/A	0	Damaged	Repair	03/31/2021	05/25/2021	Tui Mataele (OSV)	05/25/2021	05/25/2021	GOTS - Printers	Product Express	RonVan Overbeke	OAKH	1 SALLY RIDE WAY	OAKLAND	California	94621	United States of America	5106393700	GOTS - Printers	773303574085	787571615544	1	No	0	0	N/A
29	RO2021040500028	R2021040500029	R2021040500029	BDA07602	N/A	Printing Devices, Thermal Printers	Toshiba America Business Solutions	Astra 3	422040016	N/A	B-FP3D-GH30-FE-R(AD)	Printer; Astra 3 inch; Domestic; B-FP3D-GH32-AD-R-AD	Repair & Return to Customer	Delivered	N/A	N/A	N/A	N/A	Jacob Resendez (OSV)	Main Housing	Damaged	715933	Func Unit Assy-FE-FP3D (ROHS)	Part	1	N/A	Repair	04/05/2021	05/25/2021	Jacob Resendez (OSV)	05/25/2021	05/26/2021	GOTS - Printers	Product Express	NICHOLASROROS	ISPA	140 COMAC STREET	RONKONKOMA	New York	11779	United States of America	6315804900	GOTS - Printers	785524998448	787586895815	1	No	0	0	Replaced
30	RO2021040500257	R2021040500372	RITM2577196	8FKSA93800	N/A	Handhelds	Panasonic Corporation of North America	FZ-N1	195901021	N/A	FZ-N1CCCAP8M1	Handheld; PNA FZ-N1 Black Bezel; FXF Dock	Repair & Return to Customer	Ship	VI	VI015	N/A	Consumables (straps, screen protectors, stylus, etc.)	Daryl McKinney	LCD Assembly	Damaged	DHHX1647ZB	K81 - PROTECTIVE FILM (INTERNAL)	Part	1	Scanner/camera problem	Repair	05/26/2021	05/26/2021	Daryl McKinney	05/26/2021	N/A	External Panasonic Repairs	Panasonic	JeffreyTierney	2300 S Mount Prospect Rd Des Plaines	2300 S Mount Prospect Rd	Des Plaines	Illinois	60018	United States of America	8478279815	N/A	N/A	787612561761	1	Yes	0	0	Replaced
31	RO2021052600167	R2021040801315	RITM2591955	-8EKSA88016	N/A		N/A	FZ-N1	FZ-N1CCCAP8M1	N/A	N/A	FZ-N1	Repair & Return to Inventory	Scrap Vendor	N/A	N/A	N/A	N/A	Sharon Choate	N/A	N/A	N/A	N/A	N/A	0	N/A	Repair	05/26/2021	N/A	N/A	N/A	N/A	OT - Dock / Dock Supervisor	Product Freight	EddieCollins	MEMPG	555 COMPRESS DRIVE	MEMPHIS	Tennessee	38106	United States of America	0000000000	N/A	N/A	N/A	0	No	0	0	N/A
32	RO2021040800948	R2021040801315	RITM2591955	8EKSA88016	N/A	Handhelds	Panasonic Corporation of North America	FZ-N1	195901021	N/A	FZ-N1CCCAP8M1	Handheld; PNA FZ-N1 Black Bezel; FXF Dock	Repair & Return to Customer	Ship	N/A	N/A	N/A	N/A	Sharon Choate	Device	Damaged	FZ-N1CCCAP8M1	FZ-N1	Part	1	Damaged	Repair	05/21/2021	05/26/2021	Sharon Choate	05/26/2021	N/A	OT - Dock / Dock Supervisor	Product Freight	EddieCollins	MEMPG	555 COMPRESS DRIVE	MEMPHIS	Tennessee	38106	United States of America	0000000000	N/A	N/A	787611418199	1	No	0	0	Replaced
33	RO2021040900077	R2021040900078	RITM2563704	JAC6260701	N/A	Handhelds	Zebra Technologies	RS6000	607754	N/A	RS60B0-SRSF01	RING SCANNER; RS6000 FXG	Repair & Return to Customer	Awaiting Packaging	DT	DT0021	Communication	Bluetooth	Richard Greene	Mainboard	Damaged	715582	Chassis, RS6	Part	1	Damaged	Repair	05/25/2021	05/26/2021	Richard Greene	N/A	N/A	OT - Operations Technology FXG	Product Ground	AnthonyDeSantis	DENVG	8951 YOSEMITE STREET	Henderson	Colorado	80640	United States of America	0000000000	N/A	773324684812	N/A	1	Yes	0	0	Replaced
34	RO2021040900077	R2021040900078	RITM2563704	JAC6260701	N/A	Handhelds	Zebra Technologies	RS6000	607754	N/A	RS60B0-SRSF01	RING SCANNER; RS6000 FXG	Repair & Return to Customer	Awaiting Packaging	VI	VI004	N/A	Outer Casing	Richard Greene	Bottom Housing	Damaged	715760	Comfort Pad (Trigger Two Hole) (retrofit part)	Part	1	Damaged	Repair	05/25/2021	05/26/2021	Richard Greene	N/A	N/A	OT - Operations Technology FXG	Product Ground	AnthonyDeSantis	DENVG	8951 YOSEMITE STREET	Henderson	Colorado	80640	United States of America	0000000000	N/A	773324684812	N/A	1	Yes	0	0	Replaced
35	RO2021040900077	R2021040900078	RITM2563704	JAC6260701	N/A	Handhelds	Zebra Technologies	RS6000	607754	N/A	RS60B0-SRSF01	RING SCANNER; RS6000 FXG	Repair & Return to Customer	Awaiting Packaging	VI	VI004	N/A	Outer Casing	Richard Greene	Main Housing	Damaged	715414	Light Pipe, RS6	Part	1	Damaged	Repair	05/25/2021	05/26/2021	Richard Greene	N/A	N/A	OT - Operations Technology FXG	Product Ground	AnthonyDeSantis	DENVG	8951 YOSEMITE STREET	Henderson	Colorado	80640	United States of America	0000000000	N/A	773324684812	N/A	1	Yes	0	0	Replaced
36	RO2021040900077	R2021040900078	RITM2563704	JAC6260701	N/A	Handhelds	Zebra Technologies	RS6000	607754	N/A	RS60B0-SRSF01	RING SCANNER; RS6000 FXG	Repair & Return to Customer	Awaiting Packaging	VI	VI004	N/A	Outer Casing	Richard Greene	Main Housing	Missing	715430	Main seal, RS6	Part	1	Damaged	Repair	05/25/2021	05/26/2021	Richard Greene	N/A	N/A	OT - Operations Technology FXG	Product Ground	AnthonyDeSantis	DENVG	8951 YOSEMITE STREET	Henderson	Colorado	80640	United States of America	0000000000	N/A	773324684812	N/A	1	Yes	0	0	Replaced
37	RO2021040900077	R2021040900078	RITM2563704	JAC6260701	N/A	Handhelds	Zebra Technologies	RS6000	607754	N/A	RS60B0-SRSF01	RING SCANNER; RS6000 FXG	Repair & Return to Customer	Awaiting Packaging	VI	VI004	N/A	Outer Casing	Richard Greene	Main Housing	Missing	715579	Light Pipe Seal, RS6	Part	1	Damaged	Repair	05/25/2021	05/26/2021	Richard Greene	N/A	N/A	OT - Operations Technology FXG	Product Ground	AnthonyDeSantis	DENVG	8951 YOSEMITE STREET	Henderson	Colorado	80640	United States of America	0000000000	N/A	773324684812	N/A	1	Yes	0	0	Replaced
38	RO2021040900077	R2021040900078	RITM2563704	JAC6260701	N/A	Handhelds	Zebra Technologies	RS6000	607754	N/A	RS60B0-SRSF01	RING SCANNER; RS6000 FXG	Repair & Return to Customer	Awaiting Packaging	VI	VI015	N/A	Consumables (straps, screen protectors, stylus, etc.)	Richard Greene	Consumables	Non-Reusable Part	715764	Lightpipe Screws, RS6000	Part	4	Damaged	Repair	05/25/2021	05/26/2021	Richard Greene	N/A	N/A	OT - Operations Technology FXG	Product Ground	AnthonyDeSantis	DENVG	8951 YOSEMITE STREET	Henderson	Colorado	80640	United States of America	0000000000	N/A	773324684812	N/A	1	Yes	0	0	Replaced
Repair Order Raw Data

 

[JimRon]

and the report sheet

Repair Order Number	RMA Number	Product	Product Part#	Serial#	Status	Inspection Name	Defect Location	Defect Name	Consumed FedEx Part#	Consumed Item Name	Repair Action	Repair Completed By	Received Date	Repair Date	Shipped Date	Closed Date	Airport Location Code	Country	Inspection Completed By	Inbound Tracking Number	Outbound Tracking Number	Bouncer	Cycle Time	Hold Parts

 

[mumps]

According to this line of code in your macro:

If FileToOpen.Cells(iRow, 8).Value = "Product1" Then

you are looking for the text "Product1" in column H of the source file and you want to "continue for a total of eight products". Looking at your data in Post #3, I don't see the text "Product1" anywhere in column H. Please clarify in detail what the actual eight products are that you want to search. You define "iRow" based on the "Line 4 Report" sheet but then use the variable in the source sheet as seen in red above. Please clarify.

Starting with A10 until three blank rows are detected from the user defined source file.

The only place that 3 blank rows exist in the source file you posted, is at the bottom of the sheet. Are you saying that you could have 1 or more blank rows anywhere in the source sheet and that you want to set 3 consecutive blank rows if they exist, as the bottom of the range you want to copy? Also, although your data starts at row 2, you want to start the copy at row 10. Is this correct?

 

[JimRon]

The product list in column H of the source sheet. As the sheet is created automatedly based on scanned tickets there should not be blank rows in the source sheet however, with dealing with the human side of the scanners, I assume that a line might be missed by mistake. the full report contains details about who and when the data dump is pulled so in complied data starts in 10 and contain up 9 thousand rows of data. "product1" is considered a place holder for a given product to be pulled, i.e. printer, computer, scanner, radio, or Bluetooth device.

 

[mumps]

Try:

Sub Get_Data_From_File()
    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, FileChosen As Integer, wkbSource As Workbook, wkbDest As Workbook, wsDest As Worksheet
    Dim i As Long, x As Long, v1 As Variant, LastRow As Long, Header As Range, rng As Range
    Set wkbDest = ThisWorkbook
    Set wsDest = wkbDest.Sheets("Line 4 Report")
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Browse for your file & Import Range"
    flder.Filters.Add "Excel Macros Files", "*.xlsx"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set wkbSource = Workbooks.Open(FileName)
    v1 = wkbSource.Sheets(1).Range("H10", wkbSource.Sheets(1).Range("H" & Rows.Count).End(xlUp)).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(v1, 1)
            If Not .Exists(v1(i, 1)) Then
                .Add v1(i, 1), Nothing
                With wkbSource.Sheets(1)
                    LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    .Range("A1:AX" & LastRow).AutoFilter Field:=8, Criteria1:=v1(i, 1)
                    For Each rng In wsDest.Range("A1", wsDest.Range("A1").End(xlToRight))
                        Set Header = .Rows(1).Find(rng, LookIn:=xlValues, lookat:=xlWhole)
                        If Not Header Is Nothing Then
                            wsDest.Cells(wsDest.Rows.Count, rng.Column).End(xlUp).Offset(1).Resize(.[subtotal(103,A:A)] - 1).Value = Intersect(.AutoFilter.Range.Offset(9), .Columns(Header.Column)).Value
                        End If
                    Next rng
                End With
            End If
        Next i
    End With
    wkbSource.Close False
    Application.ScreenUpdating = True
End Sub

 

[JimRon]

This code is a lot different than what I started with. Not sure if I follow it correctly. Please explain how does it get from the user the file to open, how it pulls from only from the rows with the requested data (From Columns H, A,B, . . . . .,and advances to the next row of data.

 

[mumps]

Here is the macro with explanatory notes. I hope this helps.

Sub Get_Data_From_File()
    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, FileChosen As Integer, wkbSource As Workbook, wkbDest As Workbook, wsDest As Worksheet
    Dim i As Long, x As Long, v1 As Variant, LastRow As Long, Header As Range, rng As Range
    Set wkbDest = ThisWorkbook
    Set wsDest = wkbDest.Sheets("Line 4 Report")
    Set flder = Application.FileDialog(msoFileDialogFilePicker) 'opens window to select the file
    flder.Title = "Browse for your file & Import Range"
    flder.Filters.Add "Excel Macros Files", "*.xlsx"
    FileChosen = flder.Show
    FileName = flder.SelectedItems(1)
    Set wkbSource = Workbooks.Open(FileName)
    v1 = wkbSource.Sheets(1).Range("H10", wkbSource.Sheets(1).Range("H" & Rows.Count).End(xlUp)).Value 'places values in column H into an array
    With CreateObject("Scripting.Dictionary") 'creates a dicitonary to store the unique values in column H
        For i = 1 To UBound(v1, 1) 'loops through the array values
            If Not .Exists(v1(i, 1)) Then 'checks to see if the value in column H, exists in the dicitonary
                .Add v1(i, 1), Nothing 'if ti doesn't exist, it is added to the dicitonary
                With wkbSource.Sheets(1)
                    LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    .Range("A1:AX" & LastRow).AutoFilter Field:=8, Criteria1:=v1(i, 1) 'autofilters sheet1 in the source file on the unique value from column H
                    For Each rng In wsDest.Range("A1", wsDest.Range("A1").End(xlToRight)) 'loops through the headers in row 1 of the destination sheet
                        Set Header = .Rows(1).Find(rng, LookIn:=xlValues, lookat:=xlWhole) 'searches for the header in the source sheet
                        If Not Header Is Nothing Then 'when the header is found, the next line of code copies the filtered range in the found column starting at row 10 to the corresponding column in the destination sheet
                            wsDest.Cells(wsDest.Rows.Count, rng.Column).End(xlUp).Offset(1).Resize(.[subtotal(103,A:A)] - 1).Value = Intersect(.AutoFilter.Range.Offset(9), .Columns(Header.Column)).Value
                        End If
                    Next rng 'searched for the next header
                End With
            End If
        Next i 'goes to next value in the array
    End With
    wkbSource.Close False
    Application.ScreenUpdating = True
End Sub