insert new column and autofill name repeatedly based on sheet name

[tubrak]

Hello
I have multiple sheets in file , so what I want to insert new column after column date and fill name based on sheet name & repeat name up to last row , but don't insert column every time run the macro if it has already inserted column should not insert again , but if I change sheet name , then just change name for column has already inserted .

assss.xlsm
	A	B	C	D	E
1	DATE	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	INV10022	10000		10000
3	02/01/2023	INV10023	15000		25000
4	03/01/2023	INV10024	20000	2000	43000
5	04/01/2023	INV10025	25000	2000	66000
6	05/02/2023	INV10026	2000		68000
7	06/02/2023	INV10027	10000		78000
8	07/02/2023	CASH		1000	77000
9	08/02/2023	BANK		1000	76000
10	09/02/2023	CASH		8000	68000
11	10/02/2023	CASH		1200	66800
12	11/02/2023	INV10028	234	1000	66034
13	12/02/2023	INV10029	12300		78334
AA

Cell Formulas
Range	Formula
E2	E2	=C2
E3:E13	E3	=E2+C3-D3

should be

assss.xlsm
	A	B	C	D	E	F
1	DATE	NAME	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	AA	INV10022	10000		10000
3	02/01/2023	AA	INV10023	15000		25000
4	03/01/2023	AA	INV10024	20000	2000	43000
5	04/01/2023	AA	INV10025	25000	2000	66000
6	05/02/2023	AA	INV10026	2000		68000
7	06/02/2023	AA	INV10027	10000		78000
8	07/02/2023	AA	CASH		1000	77000
9	08/02/2023	AA	BANK		1000	76000
10	09/02/2023	AA	CASH		8000	68000
11	10/02/2023	AA	CASH		1200	66800
12	11/02/2023	AA	INV10028	234	1000	66034
13	12/02/2023	AA	INV10029	12300		78334
AA

Cell Formulas
Range	Formula
F2	F2	=D2
F3:F13	F3	=F2+D3-E3

another example

assss.xlsm
	A	B	C	D	E
1	DATE	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	INV1001	10000		10000
3	02/01/2023	INV1011	15000		25000
4	03/01/2023	CASH		2000	23000
5	04/01/2023	CASH		2000	21000
6	05/02/2023	INV1002	2000		23000
7	06/02/2023	INV1003	10000		33000
8	07/02/2023	BANK		1000	32000
9	08/02/2023	BANK		1000	31000
10	09/02/2023	BANK		8000	23000
11	10/02/2023	BANK		1200	21800
12	11/02/2023	INV1008	1200	1000	22000
13	12/02/2023	INV1009			22000
AS

Cell Formulas
Range	Formula
E2	E2	=C2
E3:E13	E3	=E2+C3-D3

should be

assss.xlsm
	A	B	C	D	E	F
1	DATE	NAME	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	AS	INV1001	10000		10000
3	02/01/2023	AS	INV1011	15000		25000
4	03/01/2023	AS	CASH		2000	23000
5	04/01/2023	AS	CASH		2000	21000
6	05/02/2023	AS	INV1002	2000		23000
7	06/02/2023	AS	INV1003	10000		33000
8	07/02/2023	AS	BANK		1000	32000
9	08/02/2023	AS	BANK		1000	31000
10	09/02/2023	AS	BANK		8000	23000
11	10/02/2023	AS	BANK		1200	21800
12	11/02/2023	AS	INV1008	1200	1000	22000
13	12/02/2023	AS	INV1009			22000
AS

Cell Formulas
Range	Formula
F2	F2	=D2
F3:F13	F3	=F2+D3-E3

and if I change sheet name for AS sheet to MA sheet
then will be

assss.xlsm
	A	B	C	D	E	F
1	DATE	NAME	INVOICE NUMBER	DEBIT CU	CREDIT CU	BALANCE
2	01/01/2023	MA	INV1001	10000		10000
3	02/01/2023	MA	INV1011	15000		25000
4	03/01/2023	MA	CASH		2000	23000
5	04/01/2023	MA	CASH		2000	21000
6	05/02/2023	MA	INV1002	2000		23000
7	06/02/2023	MA	INV1003	10000		33000
8	07/02/2023	MA	BANK		1000	32000
9	08/02/2023	MA	BANK		1000	31000
10	09/02/2023	MA	BANK		8000	23000
11	10/02/2023	MA	BANK		1200	21800
12	11/02/2023	MA	INV1008	1200	1000	22000
13	12/02/2023	MA	INV1009			22000
MA

Cell Formulas
Range	Formula
F2	F2	=D2
F3:F13	F3	=F2+D3-E3

 

[DanteAmor]

Try this:

Sub insertnewcolumn()
  Dim sh As Worksheet
  
  For Each sh In Sheets
    If sh.Range("A1").Value = "DATE" Then
      If sh.Range("B1").Value <> "NAME" Then
        sh.Range("B:B").Insert
        sh.Range("B1").Value = "NAME"
      End If
      sh.Range("B2:B" & sh.Range("A" & Rows.Count).End(3).Row).Value = sh.Name
    End If
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[tubrak]

Perfect !
thank you so much