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

 

[Mario_R]

Hello. This is how I would do it:

Test_1.xlsm
\_______________________/
(there is a hidden sheet)​

Test_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	SHEET NAME
3	thr											DATE
4							REF NO					10/4/2023
5			NAME				TH-001
6			MUSTAFA
MATCH

Cell Formulas
Range	Formula
L4	L4	=TODAY()

Cells with Data Validation
Cell	Allow	Criteria
A3	List	='Master data'!$A$2:$A$4

 

[Fluff]

@Mario_R please post your solution to the board, rather than just posting a link to the file. Thanks

 

[Mario_R]

No problem.

Sub Fill_Values_v3()
Dim ws As Worksheet, mData, mBrands
Application.ScreenUpdating = False
'------------------>
With Sheets("Match")
  If IsError(Evaluate("Cell(""Row"", " & .Range("A3") & "!A1)")) Then
    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = .Range("A3")
    .Range("A2").Copy ws.Range("A1:E1")
    ws.Range("A1:E1") = Array("ITEM", "NAME", "DATE", "REF NO", "BRAND")
    ws.Columns(1).HorizontalAlignment = xlCenter
    ws.Rows.RowHeight = 22
    ws.Cells.VerticalAlignment = xlCenter
  End If
'------------------>
  Set ws = Sheets(CStr(.Range("A3"))): ws.Activate
  mData = Array(.[c6].Value, .[L4].Value, .[g5].Value)
End With
'------------------>
With Sheets("Master data")
  Set mBrands = .Columns("A").Find(ws.Name, LookAt:=xlWhole)
  mBrands = Application.Transpose(.Range(mBrands(1, 2), mBrands.End(xlToRight)))
End With
'------------------>
With ws.Cells(Rows.Count, 1).End(xlUp)
  .Cells(2, 2).Resize(UBound(mBrands), 3) = mData
  .Cells(2, 5).Resize(UBound(mBrands)) = mBrands
  With ws.[a1].CurrentRegion.Columns(1)
    .Cells(2) = 1
    .Cells(2).Resize(.Rows.Count - 1).DataSeries
  End With
  ws.Columns.AutoFit
End With
'------------------>
End Sub

 

[Mussala]

@Mario_R

thanks , but there is problem! . as I said and the original code fills blank cells .

your code will skip blank cells for column B,C,D if the column A,E are filled and copy to the bottom .

every time I fill data in columns A,E and leave columns B,C,D are blank, so should filled empty cells when columns A,E are filled as the original code does it.
I hope you can fix it .

​

 

[Mario_R]

Did you try to enter data two or three times to a specific sheet?...

Because leaving cells blank does not represent anything in the present context.

 

[Mussala]

first should not copy to the bottom repeatedly as your code does it when run more time .
the idea depends on search for blank cells and filled based on cells in MATCH sheet .
this is what I got
after first run I add new data from row 7 like this

Test_1 (1).xlsm
	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
7	6				BFGH-0016
8	7				BFGH-0017
9	8				BFGH-0018
10	9				BFGH-0019
11	10				BFGH-0020
thr

but if I run again this is will give me

Test_1 (1).xlsm
	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
7	6				BFGH-0016
8	7				BFGH-0017
9	8				BFGH-0018
10	9				BFGH-0019
11	10				BFGH-0020
12	11	MUSTAFA1	10/04/2023	TH-0011	BFGH-0011
13	12	MUSTAFA1	10/04/2023	TH-0011	BFGH-0012
14	13	MUSTAFA1	10/04/2023	TH-0011	BFGH-0013
15	14	MUSTAFA1	10/04/2023	TH-0011	BFGH-0014
16	15	MUSTAFA1	10/04/2023	TH-0011	BFGH-0015
thr

the right

Test_1 (1).xlsm
	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
7	6	MUSTAFA1	10/04/2023	TH-0011	BFGH-0016
8	7	MUSTAFA1	10/04/2023	TH-0011	BFGH-0017
9	8	MUSTAFA1	10/04/2023	TH-0011	BFGH-0018
10	9	MUSTAFA1	10/04/2023	TH-0011	BFGH-0019
11	10	MUSTAFA1	10/04/2023	TH-0011	BFGH-0020
thr

and if I run more one time then will gives this

Test_1 (1).xlsm
	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
7	6	MUSTAFA1	10/04/2023	TH-0011	BFGH-0016
8	7	MUSTAFA1	10/04/2023	TH-0011	BFGH-0017
9	8	MUSTAFA1	10/04/2023	TH-0011	BFGH-0018
10	9	MUSTAFA1	10/04/2023	TH-0011	BFGH-0019
11	10	MUSTAFA1	10/04/2023	TH-0011	BFGH-0020
12	11	MUSTAFA1	10/04/2023	TH-0011	BFGH-0016
13	12	MUSTAFA1	10/04/2023	TH-0011	BFGH-0017
14	13	MUSTAFA1	10/04/2023	TH-0011	BFGH-0018
15	14	MUSTAFA1	10/04/2023	TH-0011	BFGH-0019
16	15	MUSTAFA1	10/04/2023	TH-0011	BFGH-0020
17	16	MUSTAFA1	10/04/2023	TH-0011	BFGH-0016
18	17	MUSTAFA1	10/04/2023	TH-0011	BFGH-0017
19	18	MUSTAFA1	10/04/2023	TH-0011	BFGH-0018
20	19	MUSTAFA1	10/04/2023	TH-0011	BFGH-0019
21	20	MUSTAFA1	10/04/2023	TH-0011	BFGH-0020
thr

this is wrong if there are no blank cells for columns B,C,D then should not copy at all then the right will keep this

Test_1 (1).xlsm
	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
7	6	MUSTAFA1	10/04/2023	TH-0011	BFGH-0016
8	7	MUSTAFA1	10/04/2023	TH-0011	BFGH-0017
9	8	MUSTAFA1	10/04/2023	TH-0011	BFGH-0018
10	9	MUSTAFA1	10/04/2023	TH-0011	BFGH-0019
11	10	MUSTAFA1	10/04/2023	TH-0011	BFGH-0020
thr

 

[Mario_R]

Let's see if I understand you: do you provide the data in columns A and E manually?

Actually: if so, you should only write the data in column E, since consecutive numbering of column A is very easy to implement.

 

[Mussala]

in my real project will copy from cells for other sheet ( not enter manually inside cells by me )

 

[Mario_R]

Now I understand it and clearly the auxiliary sheet that I had suggested is not necessary.

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