Auto Arrange cell values to a specific range with VBA

[bootdat]

This mission pretty seems impossible for me but I'd just wanna ask anyways if there might be some sorta solution.

I'm to copy and paste cell values into another sheet or another workbook sheet. but the manual process seems very time consuming
So was wondering if there could be an easy way out with a macro

so this is the problem

CSP TRAIN EXCEL EXTRACT (1).xls
	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
4		Flight No./Date:					DL 0156 27-Feb-2021									Arr. Date:		27-Feb-2021					Point of Loading:						JFK
5											NATURE
6	AIR WAYBILL			PIECES			WEIGHT				OF GOODS					ORG-DST				USE BY OWNER/OPERATOR
7	006-36987204		1				60.0				CONSOLIDATION					JFK-ACC
8	PMC93468DL		1		60.0
9			Shipper:			OMA LOGISTICS ROOM212 SWISSPORT KIA ACCRA ACCRA GHANA											Consignee:			OMA LOGISTICS ROOM212 SWISSPORT KIA ACCRA ACCRA GHANA
10
11	006-40155570		4		/	9	646.5		#		CONSOLIDATION					JFK-ACC
12	PMC93468DL		4		646.5
13			Shipper:			DHL PMB KIA ACCRA GHANA											Consignee:			DHL PMB KIA ACCRA GHANA
14
15	006-40247056		9				147.0				CATERING MATERIAL					JFK-ACC
16	PMC93468DL		9		147.0
17			Shipper:			DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
18
19	006-40248563		2				34.0				CATERING MATERIAL					JFK-ACC
20	PMC93468DL		2		34.0
21			Shipper:			DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
22
23	006-40248585		4				8.0				CATERING MATERIAL					JFK-ACC
24	PMC93468DL		4		8.0
25			Shipper:			DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
26
27	006-40248596		#				180.0				CATERING MATERIAL					JFK-ACC
28	PMC93468DL		#		180.0
29			Shipper:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
30
31	006-40248611		#				30.0				CATERING MATERIALS					JFK-ACC
32	PMC93468DL		#		30.0
33			Shipper:			DELTA SHIPPING COMPANY LTD. PMB KIA ACCRA GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
34
35	006-69383381		1				5.0				CONSOLIDATION					JFK-ACC
36	PMC93468DL		1		5.0
37			Shipper:			Tartines and chocolate Jam street 1 London T456FG UNITED KINGDOM											Consignee:			DELTA SHIPPING COMPANY LTD. PMB KIA ACCRA GHANA
38
39	006-69767390		8				197.0				CLOTHING,HOUSEHOLD I					JFK-ACC
40	DQL15964DL		8		197.0
41			Shipper:			SHIPPER DUBAI ROAD 3 DUBAI UNITED ARAB EMIRATES											Consignee:			Cereal investments company Ghana Ld Sanyo rd 1000 Tema GHANA
42
Sheet1

I'm to copy the details in the cells above and make it as the range below

EXCEL EXTRACT FROM GALAXY (1).xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Company Code	Flight Date	Flight No	Aircraft Reg	First Origin	Prefix	Master	House	Nature of Goods	Goods Desc	Consignee	Shipper	Pkt Dept	Dept wgt	Pkt Arriv	Pkt Wgt	Remarks
2	DL	27/02/2021	DL156	N832MH	JFK	006	36987204	WZ714458		TOOLS AND PARTS	SANDVIK MINING AND CONSTRUCTION GHANA LTD		1	60	1	60
3	DL	27/02/2021	DL156	N832MH	JFK	006	40155570	5MOU813		DRILLING MACHINE EQU	NEWMONT GOLDEN RIDGE LTD		9	1457.4	4	646.5
4	DL	27/02/2021	DL156	N832MH	JFK	006	40247056			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD		9	147	9	147
5	DL	27/02/2021	DL156	N832MH	JFK	006	40248563			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD		2	34	2	34
6	DL	27/02/2021	DL156	N832MH	JFK	006	40248585			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD		4	8	4	8
7	DL	27/02/2021	DL156	N832MH	JFK	006	40248596			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD		10	180	10	180
8	DL	27/02/2021	DL156	N832MH	JFK	006	40248611			CATERING MATERIAL	DELTA AIRLINE		10	30	10	30
9	DL	27/02/2021	DL156	N832MH	JFK	006	69383381			CONSOLIDATION	ARAMEX COMPANY		1	5	1	5
10	DL	27/02/2021	DL156	N832MH	JFK	006	69767390			CLOTHING	HAMIS MOHAMED ABDALLAH		8	197	8	197
GC NET MANIFEST

i'd like to know if there is any way out to do this with a macro.
Thank you

 

[mikerickson]

It looks like the second bit that you posted is not the desired result, but an example from other data.
If you could post the desired result from the example data that you gave, that would help us figure how to help with this.

 

[bootdat]

It looks like the second bit that you posted is not the desired result, but an example from other data.
If you could post the desired result from the example data that you gave, that would help us figure how to help with this.

I think this data below will do

CSP TRAIN EXCEL EXTRACT (1).xls
	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
4		Flight No./Date:					DL 0156 27-Feb-2021									Arr. Date:		27-Feb-2021					Point of Loading:						JFK
5											NATURE
6	AIR WAYBILL	Column1	Column2	PIECES	Column3	Column4	WEIGHT	Column5	Column6	Column7	OF GOODS	Column8	Column9	Column10	Column11	ORG-DST	Column12	Column13	Column14	USE BY OWNER/OPERATOR	Column15	Column16	Column17	Column18	Column19	Column20	Column21	Column22	Column23	Column24	Column25	Column26
7	006-36987204		1				60.0				CONSOLIDATION					JFK-ACC
8			1		60.0
9			Shipper:			OMA LOGISTICS ROOM212 SWISSPORT KIA ACCRA ACCRA GHANA											Consignee:			OMA LOGISTICS ROOM212 SWISSPORT KIA ACCRA ACCRA GHANA
10
11	006-40155570		4		/	9	646.5		1454.7		CONSOLIDATION					JFK-ACC
12			4		646.5
13			Shipper:			DHL PMB KIA ACCRA GHANA											Consignee:			DHL PMB KIA ACCRA GHANA
14
15	006-40247056		9				147.0				CATERING MATERIAL					JFK-ACC
16			9		147.0
17			Shipper:			DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
18
19	006-40248563		2				34.0				CATERING MATERIAL					JFK-ACC
20			2		34.0
21			Shipper:			DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
22
23	006-40248585		4				8.0				CATERING MATERIAL					JFK-ACC
24			4		8.0
25			Shipper:			DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
26
27	006-40248596		10				180.0				CATERING MATERIAL					JFK-ACC
28			10		180.0
29			Shipper:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
30
31	006-40248611		10				30.0				CATERING MATERIALS					JFK-ACC
32			10		30.0
33			Shipper:			DELTA SHIPPING COMPANY LTD. PMB KIA ACCRA GHANA											Consignee:			SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA
34
35	006-69383381		1				5.0				CONSOLIDATION					JFK-ACC
36			1		5.0
37			Shipper:			Tartines and chocolate Jam street 1 London T456FG UNITED KINGDOM											Consignee:			DELTA SHIPPING COMPANY LTD. PMB KIA ACCRA GHANA
38
39	006-69767390		8				197.0				CLOTHING,HOUSEHOLD I					JFK-ACC
40			8		197.0
41			Shipper:			SHIPPER DUBAI ROAD 3 DUBAI UNITED ARAB EMIRATES											Consignee:			Cereal investments company Ghana Ld Sanyo rd 1000 Tema GHANA
42
Sheet1

AND

EXCEL EXTRACT FROM GALAXY (1).xls
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Flight No	Aircraft Reg	First Origin	Prefix	Master	House	Nature of Goods	Goods Desc	Consignee	Shipper	Pkt Dept	Dept wgt	Pkt Arriv	Pkt Wgt	Remarks
2	DL156	N832MH	JFK	006	36987204			CONSOLIDATION	SANDVIK MINING AND CONSTRUCTION GHANA LTD	OMA LOGISTICS ROOM212 SWISSPORT KIA ACCRA ACCRA GHANA	1	60	1	60
3	DL156	N832MH	JFK	006	40155570			CONSOLIDATION	NEWMONT GOLDEN RIDGE LTD	DHL PMB KIA ACCRA GHANA	9	1457.4	4	646.5
4	DL156	N832MH	JFK	006	40247056			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD	DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA	9	147	9	147
5	DL156	N832MH	JFK	006	40248563			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD	DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA	2	34	2	34
6	DL156	N832MH	JFK	006	40248585			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD	DELTA AIR LINES SERVAIR GHANA LTD ACCRA ACC GHANA	4	8	4	8
7	DL156	N832MH	JFK	006	40248596			CATERING MATERIAL	SERVAIR GHANA COMPANY LTD	SERVAIR GHANA COMPANY LTD KOTOKA INTERNATIONAL AIRLINES ACC GHANA	10	180	10	180
8	DL156	N832MH	JFK	006	40248611			CATERING MATERIAL	DELTA AIRLINE	DELTA SHIPPING COMPANY LTD. PMB KIA ACCRA GHANA	10	30	10	30
9	DL156	N832MH	JFK	006	69383381			CONSOLIDATION	ARAMEX COMPANY	Tartines and chocolate Jam street 1 London T456FG UNITED KINGDOM	1	5	1	5
10	DL156	N832MH	JFK	006	69767390			CLOTHING	HAMIS MOHAMED ABDALLAH	SHIPPER DUBAI ROAD 3 DUBAI UNITED ARAB EMIRATES	8	197	8	197
GC NET MANIFEST