VBA Macro for creating multiple sheets on command

[Suavesav]

The VBA code should identify the customer name selected in the dropdown and copy it's headers into a new sheet. It then has to look for this customers data under the headers and add it to the sheet. It is not working as consistently as intended. Some sheets are only created with the Headers.


Source -

Test DSR OFS Macro All Ocean Customers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Customer Name	Test1
2
3
4	STATION	HOUSEBILL	FILEID	JOBTYPE	SHPRNO	SHPRNO1	CSEENO	CSEENADR1	CSORNO	CSORNADR1	PO	INV	BU
5	AMD		INOT53-000015	OT	0		0		0
6	AMD	AMD230382816	INOA53-005842	OA	1000671848	ABC	1000673692	GHI	1000671848	GHI
7	AMD	AMD230382816	INOA53-005842	OA	1000671848	XYZ	1000673692	VIE	1000671848	VIE
Source

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=Reference!$B$1:$CF$1

Reference -

Test DSR OFS Macro All Ocean Customers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Customer Name	Test1	Test	Bosch	Bharat Forge	APOLLO TYRES LTD	Perkins India TLM	Perkins India FGW GC CTL XQP	Perkins India LB BB IRLAM	MOTHERSON	HERTZ	JAPNA INC.,	ASHTECH	THYSSENRUPP	NATH	SPM AUTO	GEFCO
2	Header 1	STATION	SHPRNO1	SHPRNO1	Sr.No	BKG released dt, SI filled dt, VSL ETD dt	Shipment Month	Month	Sl.No	CONTAINER NO	Sr No	Sr No	Sr No	Sr No	Sr No	Shipper	Sr. No.
3	Header 2	HOUSEBILL	PO	ETD	Bfl invoice no.	Location	ECI No.	Type	Month	Invoice NO	SHPRNO1	SHPRNO1	SHPRNO1	SHPRNO1	SHPRNO1	PO	DATE
4	Header 3	FILEID	FREIGHT_RCV	FREIGHT_RCV	Shipper	Handler	Customer Name	STATE	Consignee Name	Invoice Date	Cnee	Cnee	Cnee	Cnee	Cnee	Rcvd	Current Remarks
5	Header 4	JOBTYPE	Pickup	HOUSEBILL	HBL No.	NRA dt	Pickup location	Customer Name	Pickup location	Shipping line	Booking	Booking	Booking	Booking	Booking	Pickup	Shipper
6	Header 5	SHPRNO	Coloader	Coloader	Customer	NRA number	Port of Loading	Pickup location	Port of Loading	Sailing Date	MBL	MBL	MBL	MBL	MBL	Coloader	Consignee
7	Header 6	SHPRNO1	POL	POL	Sector	JOB #	POD Name	Port of Loading	Port of Discharge	ETA	Job	Console no	Job	Job	Job	POL	Job #
8	Header 7	CSEENO	POD	POD	POD	MBL #	Country of Entry	POD Name	Country of Destination	DELIVERY DATE	HBL	Job	HBL	HBL	HBL	POD	BL #
9	Header 8	CSEENADR1	Inv	Vessel	Final Destinatooon	HBL #	Continents	Country of Entry	Freight Term		Shipper Inv	HBL	Shipper Inv	Shipper Inv	Shipper Inv	Inv	Terms
10	Header 9	CSORNO	Job	ETD	Dispatch dt from BFL	ETD	Container type	Continents	Delivery terms		POL	Shipper Inv	POL	POL	POL	Job	Contract no.
11	Header 10	CSORNADR1	SB	ATD	Stuffing location	ETA	No Of Tues	Container type	Shipment Mode		POD	POL	POD	POD	POD	SB	Co-loader/Carrier boking no.
Reference

Intended result

Test DSR OFS Macro All Ocean Customers.xlsm
	A	B	C	D	E	F	G	H	I	J
1	STATION	HOUSEBILL	FILEID	JOBTYPE	SHPRNO	SHPRNO1	CSEENO	CSEENADR1	CSORNO	CSORNADR1
2	BOM	SOF02394637	INOD51-061123	OD	1000044673	CEVA	1000392439	DURAVIT INDIA PVT LTD	1000392439	DURAVIT INDIA PVT LTD
20230710_140326

 

[OaklandJim]

I do not see a BOM in the data, only in the result. Do you specify which customer in B1 of source? What is the reference data used for?

 

[OaklandJim]

I tried to understand the objective but I cannot. Please try again to describe the way that the results are generated.