exclude TOTAL row & add data before TOTAL row and change calculation TOTAL row

[Alaa mg]

Hello
I need copy data from CUSTOMERS sheet to BALANCES sheet before TOTAL row with exclude TOTAL row for CUSTOMERS sheet when copy data ,also change TOTAL row in BALANCES sheet after bring data from CUSTOMERS sheet

ALL.xlsm
	A	B	C	D	E	F
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6,000.00	7,000.00	-1,000.00
3	2	OPENING BALANCE 27/07/2024	MLA	6,000.00	10,000.00	-4,000.00
4	TOTAL			12,000.00	17,000.00	-5,000.00
BALANCES

ALL.xlsm
	A	B	C	D	E
1	ITEM	NAME	DEBIT	CREDIT	BALANCE
2	1	ALAA	4,000.00	1,000.00	3,000.00
3	2	MAAL	2,000.00	1,200.00	800.00
4	TOTAL		6,000.00	2,200.00	3,800.00
CUSTOMERS

result

ALL.xlsm
	A	B	C	D	E	F
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6,000.00	7,000.00	-1,000.00
3	2	OPENING BALANCE 27/07/2023	MLA	6,000.00	10,000.00	-4,000.00
4	3	OPENING BALANCE 27/07/2023	ALAA	4,000.00	1,000.00	3,000.00
5	4	OPENING BALANCE 27/07/2023	MAAL	2,000.00	1,200.00	800.00
6	TOTAL			18,000.00	19,200.00	-1,200.00
BALANCES

so in TOTAL row should sum D:F and when bring data from CUSTOMERS sheet should add OPEN BALANCE word & DATE in column B
my real project will be 3500 rows for each sheet
thanks

 

[Peter_SSs]

See if this could work for you.

First, put this formula in the TOTAL row of column D in 'BALANCES' and copy across to column F

Alaa mg.xlsm
	A	B	C	D	E	F
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6000	7000	-1000
3	2	OPENING BALANCE 27/07/2024	MLA	6000	10000	-4000
4	TOTAL			12000	17000	-5000
BALANCES

Cell Formulas
Range	Formula
D4:F4	D4	=SUM(D2:INDEX(D:D,ROW()-1))

Now run this code

Sub Add_New_Data()
  Dim nr As Long, rws As Long
  Dim rCust As Range
  
  With Sheets("CUSTOMERS")
    Set rCust = .Range("B2", .Range("E" & Rows.Count).End(xlUp).Offset(-1))
    rws = rCust.Rows.Count
  End With
  Application.ScreenUpdating = False
  With Sheets("BALANCES")
    nr = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(nr).Resize(rws).Insert
    With .Range("A" & nr).Resize(rws)
      rCust.Copy Destination:=.Offset(, 2)
      .Offset(, 1).Value = "OPENING BALANCE " & Format(Date, "dd/mm/yyyy")
      .FormulaR1C1 = "=R[-1]C+1"
      .Value = .Value
    End With
  End With
  Application.ScreenUpdating = True
End Sub

Here is my sheet after I have done that.

Alaa mg.xlsm
	A	B	C	D	E	F
1	ITEM	DETAILS	NAME	DEBIT	CREDIT	BALANCE
2	1	OPENING BALANCE 27/07/2023	LLA	6000	7000	-1000
3	2	OPENING BALANCE 27/07/2024	MLA	6000	10000	-4000
4	3	OPENING BALANCE 30/07/2023	ALAA	4000	1000	3000
5	4	OPENING BALANCE 30/07/2023	MAAL	2000	1200	800
6	TOTAL			18000	19200	-1200
BALANCES

Cell Formulas
Range	Formula
D6:F6	D6	=SUM(D2:INDEX(D:D,ROW()-1))

 

[Alaa mg]

Hi Peter
unfortunately the BLANACES sheet will create by another macro without any formula , if I put the formula my macro will delete it.
is there way without write formula ,please?

 

[Peter_SSs]

Try this

Sub Add_New_Data_v2()
  Dim nr As Long, rws As Long
  Dim rCust As Range
  
  With Sheets("CUSTOMERS")
    Set rCust = .Range("B2", .Range("E" & Rows.Count).End(xlUp).Offset(-1))
    rws = rCust.Rows.Count
  End With
  Application.ScreenUpdating = False
  With Sheets("BALANCES")
    nr = .Range("A" & Rows.Count).End(xlUp).Row
    .Rows(nr).Resize(rws).Insert
    With .Range("A" & nr).Resize(rws)
      rCust.Copy Destination:=.Offset(, 2)
      .Offset(, 1).Value = "OPENING BALANCE " & Format(Date, "dd/mm/yyyy")
      .FormulaR1C1 = "=R[-1]C+1"
      .Value = .Value
    End With
    With .Range("D" & nr + rws).Resize(, 3)
      .FormulaR1C1 = "=sum(R2C:R[-1]C)"
      .Value = .Value
    End With
  End With
  Application.ScreenUpdating = True
End Sub

 

[Alaa mg]

great !
thank you so much .

 

[Peter_SSs]

Cheers. You are welcome.