Pulling data from master copy into multiple sheets

[ThiccNugg]

Hi, I need a VBA code that copies data from a 'master sheet' and pastes it into the relevant sheets.

The 'master sheet' is named "BCA" and then I have many sheets that are renamed according to the value of ("AZ1") in their respective sheet. What I need is a VBA code to, for each sheet, search in the master sheet in column ("AC") for cells with value equal to the sheets ("AZ1") value and copy all relevant rows and then paste them in the sheet in cell ("A2"). Now as this this sheet is used daily there can be no set range as the amount of data changes from day to day.

Is this possible to do and if so could I please have some help. Thanks

 

[JamesCanale]

Does it have to be macros? You have the powerful 365.
BCA Sheet, with data formatted as table:

MrExcelPlayground4.xlsx
	AA	AB	AC
1	Name	Count	Type
2	Jamie	55	a
3	Fred	435	b
4	Harry	65	c
5	John	76	a
6	Sally	56	b
7	Mary	756	c
8	Maverick	34	a
9	Goose	6	b
10	Iceman	534	c
11	George	12	a
BCA

One of the sheets, with the filtered data:

MrExcelPlayground4.xlsx
	AV	AW	AX	AY	AZ
1	Name	Count	Type		a
2	Jamie	55	a
3	John	76	a
4	Maverick	34	a
5	George	12	a
Sheet24

Cell Formulas
Range	Formula
AV2:AX5	AV2	=FILTER(Table2,Table2[Type]=AZ1)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
BCATable	=Table2	AV2