Add item in first digits with create space after it based on sheet name

[Maklil]

Hello
I want add item in first digit and make one space based on sheet name also write INVOICE NO word after sheet name word and make one space after it for each sheet in column (B) and if there is already existed after add from first time then don't add again . I will add new sheets , but I want macro to implement for specific sheets , not all of sheets in workbook.

NEW.xlsx
	A	B	C	D	E	F	G
1	DATE	INV NNO	ID	CASE	QTY	UNIT PRICE	BALANCE
2	21/08/2023	VT NO 1000	FOO LL 1000	PAID	220.00	123.00	27,060.00
3	21/08/2023	VT NO 1000	GNOO HH 1200	PAID	100.00	122.00	12,200.00
4	21/08/2023	VT NO 1000	AS100-12	PAID	120.00	111.00	13,320.00
5	21/08/2023	VT NO 1001	MGFH GA-100	NOT PAID	100.00	110.00	11,000.00
6	21/08/2023	VT NO 1001	MGFH GA-101	NOT PAID	120.00	111.00	13,320.00
7	21/08/2023	VT NO 1001	MGFH GA-102	NOT PAID	110.00	110.00	12,100.00
8	21/08/2023	VT NO 1001	MGFH GA-103	NOT PAID	12.00	100.00	1,200.00
9	21/08/2023	VT NO 1002	SSFOO 1000 MN1	NOT PAID	20.00	124.00	2,480.00
10
11	21/08/2023	VT NO 1002	SSFOO 1000 MN2	NOT PAID	30.00	122.00	3,660.00
12	21/08/2023	VT NO 1002	SSFOO 1000 MN3	NOT PAID	24.00	124.00	2,976.00
13	21/08/2023	VT NO 1002	SSFOO 1000 MN4	NOT PAID	22.00	125.00	2,750.00
14	21/08/2023	VT NO 1002	SSFOO 1000 MN5	NOT PAID	10.00	144.00	1,440.00
15	21/08/2023	VT NO 1003	OOIL AS-100	PAID	10.00	145.00	1,450.00
16	21/08/2023	VT NO 1003	OOIL AS-101	PAID	25.00	150.00	3,750.00
17	21/08/2023	VT NO 1003	OOIL AS-102	PAID	40.00	155.00	6,200.00
18	21/08/2023	VT NO 1003	OOIL AS-103	PAID	55.00	160.00	8,800.00
19
20	21/08/2023	VT NO 1003	OOIL AS-104	PAID	70.00	165.00	11,550.00
21	21/08/2023	VT NO 1003	OOIL AS-105	PAID	85.00	170.00	14,450.00
22	21/08/2023	VT NO 1003	OOIL AS-106	PAID	100.00	175.00	17,500.00
23	21/08/2023	VT NO 1003	OOIL AS-107	PAID	115.00	180.00	20,700.00
24	22/08/2023	VT NO 1004	OOIL AS-108	PAID	130.00	185.00	24,050.00
25	22/08/2023	VT NO 1004	OOIL AS-109	PAID	145.00	190.00	27,550.00
BUYING

Cell Formulas
Range	Formula
G20:G25,G15:G18,G2:G8	G2	=E2*F2
G9,G11:G14	G9	=F9*E9

NEW.xlsx
	A	B	C	D	E	F	G
1	DATE	INV NNO	ID	CASE	QTY	UNIT PRICE	BALANCE
2	21/08/2023	ST NO 1000	GNOO HH 1200	PAID	10.00	150.00	1,500.00
3	21/08/2023	ST NO 1000	AS100-12	PAID	10.00	130.00	1,300.00
4	21/08/2023	ST NO 1001	MGFH GA-103	NOT PAID	15.00	130.00	1,950.00
5	21/08/2023	ST NO 1002	SSFOO 1000 MN1	NOT PAID	2.00	140.00	280.00
6
7	21/08/2023	ST NO 1002	SSFOO 1000 MN2	NOT PAID	12.00	145.00	1,740.00
8	21/08/2023	ST NO 1002	SSFOO 1000 MN3	NOT PAID	10.00	145.00	1,450.00
9	24/08/2023	VT NO 1003	OOIL AS-100	PAID	10.00	145.00	1,450.00
10	24/08/2023	VT NO 1003	OOIL AS-101	PAID	25.00	150.00	3,750.00
11
12	24/08/2023	VT NO 1003	OOIL AS-102	PAID	40.00	155.00	6,200.00
13	24/08/2023	VT NO 1003	OOIL AS-103	PAID	55.00	160.00	8,800.00
14	24/08/2023	VT NO 1003	OOIL AS-104	PAID	70.00	165.00	11,550.00
15	24/08/2023	VT NO 1003	OOIL AS-105	PAID	85.00	170.00	14,450.00
16	24/08/2023	VT NO 1003	OOIL AS-106	PAID	100.00	175.00	17,500.00
17	24/08/2023	VT NO 1003	OOIL AS-107	PAID	115.00	180.00	20,700.00
SELLING

Cell Formulas
Range	Formula
G12:G17,G9:G10,G2:G4	G2	=E2*F2
G5,G7:G8	G5	=F5*E5

result

NEW.xlsx
	A	B	C	D	E	F	G
1	DATE	INV NNO	ID	CASE	QTY	UNIT PRICE	BALANCE
2	21/08/2023	BUYING INVOICE NO VT NO 1000	FOO LL 1000	PAID	220.00	123.00	27,060.00
3	21/08/2023	BUYING INVOICE NO VT NO 1000	GNOO HH 1200	PAID	100.00	122.00	12,200.00
4	21/08/2023	BUYING INVOICE NO VT NO 1000	AS100-12	PAID	120.00	111.00	13,320.00
5	21/08/2023	BUYING INVOICE NO VT NO 1001	MGFH GA-100	NOT PAID	100.00	110.00	11,000.00
6	21/08/2023	BUYING INVOICE NO VT NO 1001	MGFH GA-101	NOT PAID	120.00	111.00	13,320.00
7	21/08/2023	BUYING INVOICE NO VT NO 1001	MGFH GA-102	NOT PAID	110.00	110.00	12,100.00
8	21/08/2023	BUYING INVOICE NO VT NO 1001	MGFH GA-103	NOT PAID	12.00	100.00	1,200.00
9	21/08/2023	BUYING INVOICE NO VT NO 1002	SSFOO 1000 MN1	NOT PAID	20.00	124.00	2,480.00
10
11	21/08/2023	BUYING INVOICE NO VT NO 1002	SSFOO 1000 MN2	NOT PAID	30.00	122.00	3,660.00
12	21/08/2023	BUYING INVOICE NO VT NO 1002	SSFOO 1000 MN3	NOT PAID	24.00	124.00	2,976.00
13	21/08/2023	BUYING INVOICE NO VT NO 1002	SSFOO 1000 MN4	NOT PAID	22.00	125.00	2,750.00
14	21/08/2023	BUYING INVOICE NO VT NO 1002	SSFOO 1000 MN5	NOT PAID	10.00	144.00	1,440.00
15	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-100	PAID	10.00	145.00	1,450.00
16	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-101	PAID	25.00	150.00	3,750.00
17	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-102	PAID	40.00	155.00	6,200.00
18	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-103	PAID	55.00	160.00	8,800.00
19
20	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-104	PAID	70.00	165.00	11,550.00
21	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-105	PAID	85.00	170.00	14,450.00
22	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-106	PAID	100.00	175.00	17,500.00
23	21/08/2023	BUYING INVOICE NO VT NO 1003	OOIL AS-107	PAID	115.00	180.00	20,700.00
24	22/08/2023	BUYING INVOICE NO VT NO 1004	OOIL AS-108	PAID	130.00	185.00	24,050.00
25	22/08/2023	BUYING INVOICE NO VT NO 1004	OOIL AS-109	PAID	145.00	190.00	27,550.00
BUYING

Cell Formulas
Range	Formula
G20:G25,G15:G18,G2:G8	G2	=E2*F2
G9,G11:G14	G9	=F9*E9

NEW.xlsx
	A	B	C	D	E	F	G
1	DATE	INV NNO	ID	CASE	QTY	UNIT PRICE	BALANCE
2	21/08/2023	SELLING INVOICE NO ST NO 1000	GNOO HH 1200	PAID	10.00	150.00	1,500.00
3	21/08/2023	SELLING INVOICE NO ST NO 1000	AS100-12	PAID	10.00	130.00	1,300.00
4	21/08/2023	SELLING INVOICE NO ST NO 1001	MGFH GA-103	NOT PAID	15.00	130.00	1,950.00
5	21/08/2023	SELLING INVOICE NO ST NO 1002	SSFOO 1000 MN1	NOT PAID	2.00	140.00	280.00
6
7	21/08/2023	SELLING INVOICE NO ST NO 1002	SSFOO 1000 MN2	NOT PAID	12.00	145.00	1,740.00
8	21/08/2023	SELLING INVOICE NO ST NO 1002	SSFOO 1000 MN3	NOT PAID	10.00	145.00	1,450.00
9	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-100	PAID	10.00	145.00	1,450.00
10	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-101	PAID	25.00	150.00	3,750.00
11
12	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-102	PAID	40.00	155.00	6,200.00
13	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-103	PAID	55.00	160.00	8,800.00
14	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-104	PAID	70.00	165.00	11,550.00
15	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-105	PAID	85.00	170.00	14,450.00
16	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-106	PAID	100.00	175.00	17,500.00
17	24/08/2023	SELLING INVOICE NO ST NO 1003	OOIL AS-107	PAID	115.00	180.00	20,700.00
SELLING

Cell Formulas
Range	Formula
G12:G17,G9:G10,G2:G4	G2	=E2*F2
G5,G7:G8	G5	=F5*E5

 

[kvsrinivasamurthy]

Try this Macro. Works on the active sheet.

Sub AddItem()
Dim Sh$, Shadd$, Lr&, M

Sh = ActiveSheet.Name: Shadd = Sh & " INVOICE NO "
Lr = Range("A" & Rows.Count).End(xlUp).Row

Range("B2:B" & Lr) = Evaluate("IFerror(IF(Left(B2:B25,find("" "",B2:B" & Lr & ")-1)=""" & Sh & """,B2:B" & Lr & ",""" & Shadd & """&B2:B25),"""")")

End Sub

 

[Maklil]

thanks ,
but I would implement for specific sheet , not active sheet !

I will add new sheets , but I want macro to implement for specific sheets , not all of sheets in workbook.

also in this part

M <span>=</span> Evaluate<span>(</span><span>"IFerror(IF(Left(B2:B25</span>

I would range is dynamic when add new data for each sheet. the data are not finish until B25.

 

[Maklil]

deleted

 

[kvsrinivasamurthy]

Code modified. Input box provided. Enter the name of the sheet. Range will be automatically taken care by macro.

Sub AddItem()
Dim Sh$, Shadd$, Lr&, M

Sh = InputBox("Enter the name of the sheet: ", "SHEET NAME")

If Sh = "" Then Exit Sub
Shadd = Sh & " INVOICE NO "
Lr = Range("A" & Rows.Count).End(xlUp).Row

With Range("B2:B" & Lr)
.Value = Evaluate("IFerror(IF(Left(B2:B" & Lr & ",find("" "",B2:B" & Lr & ")-1)=""" & Sh & """,B2:B" & Lr & ",""" & Shadd & """&B2:B" & Lr & "),"""")")
.WrapText = False
.EntireColumn.AutoFit
End With

End Sub

 

[Maklil]

may put sheets in array and implement at once instead of using inputbox to do for each sheet individually like this ("BUYING","SELLING"....),please?

 

[kvsrinivasamurthy]

Try. I have added 2 names in array. If required add further names. No other changes are required in code when more sheets are added.

Sub AddItem()
Dim Shadd$, Rng$, Lr&, Sh, Shnames, M

Shnames = Array("SELLING", "BUYING")
For Each Sh In Shnames
If Sh = "" Then Exit Sub
With Sheets(Sh)
Shadd = Sh & " INVOICE NO "
Lr = .Range("A" & Rows.Count).End(xlUp).Row
Rng = "'" & Sh & "'!B2:B" & Lr
    With .Range("B2:B" & Lr)
    .Value = Evaluate("IFerror(IF(Left(" & Rng & ",find("" ""," & Rng & ")-1)=""" & Sh & """," & Rng & ",""" & Shadd & """&" & Rng & "),"""")")
    .WrapText = False
    .EntireColumn.AutoFit
    End With
End With
Next Sh
End Sub

 

[Maklil]

Thank you so much.

 

[kvsrinivasamurthy]

I have improved the code to avoid bugs. Try this.

Sub AddItem()
Dim Shadd$, Rng$, Lr&, Sh, Shnames
Shnames = Array("SELLING", "BUYING")
For Each Sh In Shnames
If Sh = "" Then Exit Sub
With Sheets(Sh)
Shadd = Sh & " INVOICE NO "
Lr = .Range("A" & Rows.Count).End(xlUp).Row
Rng = "'" & Sh & "'!B2:B" & Lr
    With .Range("B2:B" & Lr)
    .Value = Evaluate("IFerror(If(" & Rng & "="""","""",IF(Left(" & Rng & ",Len(""" & Sh & """))=""" & Sh & """," & Rng & ",""" & Shadd & """&" & Rng & ")),"""")")
    .WrapText = False
    .EntireColumn.AutoFit
    End With
End With
Next Sh
End Sub