VBA code to filter data from cell and save as filtered data into a new workbook with specific file name based on cell value

[nikhil0311]

There are 2 tabs in this file. 1 - Mapping sheet which has filter data column and file name column. 2nd tab is "Required data" where raw data is stored.

Objective - create a macro to filter data and save as into a new workbook with file name based on cell value.

step 1 - go to mapping sheet column F "filter key column" and copy single cell value f2 i.e. "cognizant" and filter this cell value in tab "required data" column A. (need a loop function here as data count is dynamic)
step 2 - now from "required data" tab, we want to copy the filtered data (from column B1 onwards and visible cells only since we have a column grouping) into a new workbook and the file name of the new workbook should be based on cell value (mapping sheet column G, cell G2) (need a loop function here as data count is dynamic)
step 3 - file can be saved on desktop in a new folder which can be named as "Output".

you can also refer the below file images for your further action. Thanks a lot in advance and please let me know if you have any questions!!

Mapping sheet

VAM.xlsb
	A	B	C	D	E	F	G
1	Do Not Delete	Scope		DO Not Delete		Filter Key	File Name
2	xxx	Y		aaaa		Cognizant	China
3	xxx	N		vvvv		Booking Holding	Japan
4	xxxxxx	Y		bbbb		Berry Global	Aus
5	xxxxxx	N		hhhh		Mc Donals	NZ
6				jjjj		American Tourister	KR
Mapping

sheet 2 "Required data"

VAM.xlsb
	A	B	C		E	F			I				M			P		R		T			W	X	Y			AB	AC
1	Filter Key	Ref	UP		Legal Entity	Sin			AC No				Lead			Sales P		Coverage		Channel			Product	Thresh	Currency			Date	Term
2	Cognizant	1	Cognizant		A	aassdd			1211123				ABC			zz		aaaa		dddd			eeee	csfdfdf	85			8/4/2022	sdfs
3	Booking Holding	2	Booking Holding		d	d			s				vgdsvd			sf		d		d			d	s	20			vgdsvd	sfsf
4	Berry Global	3	Berry Global		x	ss			d				dfd			sfs		x		x			x	d	11			dfd	fsf
5	Mc Donals	4	Mc Donals		c	d			d				dafad			dfad		c		c			c	d	21			dafad	ssfs
6	American Tourister	5	American Tourister		f	d			d				sfa			assf		f		f			f	d	15			sfa	sfsf
2 Required Data

output file save as into a new workbook

VAM.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Ref	UP	Legal Entity	Sin	AC No	Lead	Sales P	Coverage	Channel	Product	Thresh	Currency	Date	Term
2	1	Cognizant	A	aassdd	1211123	ABC	zz	aaaa	dddd	eeee	csfdfdf	85	8/4/2022	sdfs
China