Match sheet name for cell with sheet name and repeat filling blank cells

[Mussala]

Hi experts,
I have multiple sheets in my file , but I want loop throughout theses sheets(mst,secv,thr) based on matching cell H6 with MATCH sheet .
so should search for matched sheet name with cell H6 if it's matched , then should fill blank cells & repeat them for columns B,C,D based on cells J9,N8,S7 where are existed in MATCH sheet .
if there columns B,C,D are already filled, then should ignore columns B,C,D are filled , just search for next blank cells in column B,C, and fill them.
I have this code for @Peter_Ss but this work for just one sheet.

Sub Fill_Values_v2()
  Dim ws2 As Worksheet
  Dim lrC As Long, lrD As Long
 
  Set ws2 = Sheets("Sheet2")
  With Sheets("Sheet1")
    lrC = .Range("A" & Rows.Count).End(xlUp).Row
    lrD = .Range("B" & Rows.Count).End(xlUp).Row
    If lrC > lrD Then
      With .Range("B" & lrD + 1 & ":D" & lrC)
        .Value = Array(ws2.Range("J9").Value, ws2.Range("S7").Value, ws2.Range("N8").Value)
      End With
    End If
  End With
End Sub

original data

Microsoft Excel .xlsx
	A	B	C	D	E
1	ITEM	NAME	DATE	REF NO	BRAND
2	1				BFGH-001
3	2				BFGH-002
4	3				BFGH-003
5	4				BFGH-004
6	5				BFGH-005
7	6				BFGH-006
8	7				BFGH-007
9	8				BFGH-008
10	9				BFGH-009
11	10				BFGH-010
mst

Microsoft Excel.xlsx
	A	B	C	D	E
1	ITEM	NAME	DATE	REF NO	BRAND
2	1				BFGH-0110
3	2				BFGH-0111
4	3				BFGH-0112
5	4				BFGH-0113
6	5				BFGH-0114
7	6				BFGH-0115
8	7				BFGH-0116
secv

Microsoft Excel.xlsx
	A	B	C	D	E
1	ITEM	NAME	DATE	REF NO	BRAND
2	1				BFGH-0011
3	2				BFGH-0012
4	3				BFGH-0013
5	4				BFGH-0014
6	5				BFGH-0015
thr

Microsoft Excel .xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S
5	SHEET NAME
6												DATE
7							REF NO
8			NAME
9
MATCH

example and result for sheet mst

Microsoft Excel .xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S
4
5	SHEET NAME
6	mst											DATE
7							REF NO					10/04/2023
8			NAME				RE-001
9			MUSSA
MATCH

Cell Formulas
Range	Formula
S7	S7	=TODAY()

should be

Microsoft Excel .xlsx
	A	B	C	D	E
1	ITEM	NAME	DATE	REF NO	BRAND
2	1	MUSSA	10/04/2023	RE-001	BFGH-001
3	2	MUSSA	10/04/2023	RE-001	BFGH-002
4	3	MUSSA	10/04/2023	RE-001	BFGH-003
5	4	MUSSA	10/04/2023	RE-001	BFGH-004
6	5	MUSSA	10/04/2023	RE-001	BFGH-005
7	6	MUSSA	10/04/2023	RE-001	BFGH-006
8	7	MUSSA	10/04/2023	RE-001	BFGH-007
9	8	MUSSA	10/04/2023	RE-001	BFGH-008
10	9	MUSSA	10/04/2023	RE-001	BFGH-009
11	10	MUSSA	10/04/2023	RE-001	BFGH-010
mst

another example for sheet thr

Microsoft Excel .xlsx
	H	I	J	K	L	M	N	O	P	Q	R	S
5	SHEET NAME
6	thr											DATE
7							REF NO					10/04/2023
8			NAME				TH-001
9			MUSTAFA
MATCH

Cell Formulas
Range	Formula
S7	S7	=TODAY()

should be

Microsoft Excel .xlsx
	A	B	C	D	E
1	ITEM	NAME	DATE	REF NO	BRAND
2	1	MUSTAFA	10/04/2023	TH-001	BFGH-0011
3	2	MUSTAFA	10/04/2023	TH-001	BFGH-0012
4	3	MUSTAFA	10/04/2023	TH-001	BFGH-0013
5	4	MUSTAFA	10/04/2023	TH-001	BFGH-0014
6	5	MUSTAFA	10/04/2023	TH-001	BFGH-0015
thr

 

[Mussala]

In fact: you don't even need the code that checks if the sheet exists and creates it if it doesn't.

ok but maybe shows error if the sheet name is not matched ,then should show "message the sheet is not existed".

 

[Mario_R]

Try with:

Sub Fill_Values_v3()
Dim ws As Worksheet, mData, C As Range, D As Range
Application.ScreenUpdating = False
'------------------>
With Sheets("Match")
  If IsError(Evaluate("Cell(""Row"", " & .Range("A3") & "!A1)")) Then MsgBox "The sheet does not exist.": Exit Sub
  Set ws = Sheets(CStr(.Range("A3"))): ws.Activate
  mData = Array(.[c6].Value, .[L4].Value, .[g5].Value)
End With
'------------------>
Set C = ws.Cells(Rows.Count, "B").End(xlUp)
Set D = ws.Cells(Rows.Count, "E").End(xlUp)
If C.Row = D.Row Then MsgBox "There are no cells to fill.": Exit Sub
'------------------>
ws.Range(C(2), D(, 0)) = mData
ws.Range("A2") = 1: ws.Range("A2:A" & D.Row).DataSeries
End Sub

 

[Mussala]

fantastic !
many thanks buddy!

 

[Mario_R]

fantastic !
many thanks buddy!

It ended up being a lot easier than I thought at first, right?...