Macro to populate amounts in list based on adjacent amount for last row and previous lastrow

[Mussala]

Hello,
I would macro to create report column H:K with the same formatting and borders and headers .
so should brings the date based on column A before TOTAL row and put in column H and brings balance in TOTAL row from column E and put in column I and brings amount from column F is (always existed before TOTAL row) and put in column J and column K = column I+ column J and insert TOTAL row to sum each column

Q5‫‬.xlsx
	A	B	C	D	E	F
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL
2	19/06/2023	INV001	4,220.00		4,220.00
3	19/06/2023	INV002	5,000.00		9,220.00
4	19/06/2023	INV003	1,920.00		11,140.00
5	19/06/2023	INV004	270.00		11,410.00
6	19/06/2023	INV005	4,220.00		15,630.00
7	19/06/2023	INV006	3,700.00		19,330.00
8	19/06/2023	INV007	2,460.00		21,790.00
9	19/06/2023	INV008	1,000.00		22,790.00
10	19/06/2023	EXPENSES		44.00	22,746.00
11	19/06/2023	EXPENSES		30.00	22,716.00
12	19/06/2023	EXPENSES		65.00	22,651.00
13	19/06/2023	EXPENSES		11.00	22,640.00	1,700.00
14	TOTAL		22,790.00	150.00	22,640.00
ASS

should be

Q5‫‬.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL		DATE	BALANCE	REAL	NET
2	19/06/2023	INV001	4,220.00		4,220.00			19/06/2023	22,640.00	1,700.00	24,340.00
3	19/06/2023	INV002	5,000.00		9,220.00			TOTAL	22,640.00	1,700.00	24,340.00
4	19/06/2023	INV003	1,920.00		11,140.00
5	19/06/2023	INV004	270.00		11,410.00
6	19/06/2023	INV005	4,220.00		15,630.00
7	19/06/2023	INV006	3,700.00		19,330.00
8	19/06/2023	INV007	2,460.00		21,790.00
9	19/06/2023	INV008	1,000.00		22,790.00
10	19/06/2023	EXPENSES		44.00	22,746.00
11	19/06/2023	EXPENSES		30.00	22,716.00
12	19/06/2023	EXPENSES		65.00	22,651.00
ASS

Cell Formulas
Range	Formula
K2	K2	=I2+J2
I3:K3	I3	=SUM(I2)

another example

Q5.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL		DATE	BALANCE	REAL	NET
2	20/06/2023	INV001	4,220.00		4,220.00			19/06/2023	22,640.00	1,700.00	24,340.00
3	20/06/2023	INV002	5,000.00		9,220.00			TOTAL	22,640.00	1,700.00	24,340.00
4	20/06/2023	INV003	1,920.00		11,140.00
5	20/06/2023	INV004	270.00		11,410.00
6	20/06/2023	INV005	4,220.00		15,630.00
7	20/06/2023	INV006	3,700.00		19,330.00
8	20/06/2023	INV007	2,460.00		21,790.00
9	20/06/2023	INV008	1,000.00		22,790.00
10	20/06/2023	EXPENSES		44.00	22,746.00
11	20/06/2023	EXPENSES		30.00	22,716.00
12	20/06/2023	EXPENSES		65.00	22,651.00
13	20/06/2023	EXPENSES		11.00	22,640.00
14	20/06/2023	INV009	5,000.00		27,640.00	-3,300.00
15	TOTAL		27,790.00	150.00	27,640.00
ASS

Cell Formulas
Range	Formula
K2	K2	=I2+J2
I3:K3	I3	=SUM(I2)

should be

Q5.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	DATE	DESCRIBE	DEBIT	CREDIT	BALANCE	REAL		DATE	BALANCE	REAL	NET
2	20/06/2023	INV001	4,220.00		4,220.00			19/06/2023	22,640.00	1,700.00	24,340.00
3	20/06/2023	INV002	5,000.00		9,220.00			20/06/2023	27,640.00	-3,300.00	24,340.00
4	20/06/2023	INV003	1,920.00		11,140.00			TOTAL	50,280.00	-1,600.00	48,680.00
5	20/06/2023	INV004	270.00		11,410.00
6	20/06/2023	INV005	4,220.00		15,630.00
7	20/06/2023	INV006	3,700.00		19,330.00
8	20/06/2023	INV007	2,460.00		21,790.00
9	20/06/2023	INV008	1,000.00		22,790.00
10	20/06/2023	EXPENSES		44.00	22,746.00
11	20/06/2023	EXPENSES		30.00	22,716.00
12	20/06/2023	EXPENSES		65.00	22,651.00
13	20/06/2023	EXPENSES		11.00	22,640.00
14	20/06/2023	INV009	5,000.00		27,640.00	-3,300.00
15	TOTAL		27,790.00	150.00	27,640.00
ASS

Cell Formulas
Range	Formula
K2:K3	K2	=I2+J2
I4:K4	I4	=SUM(I2:I3)

I just would macro , I don't want solution by Power Query or Pivot Table at all.
thanks

 

[DanteAmor]

Try the following macro. Consider that each time you run the macro, one row will be incremented in columns H to K.
The macro also assumes that the headers in H1 to K1 already exist.
The macro puts the result of the formulas, but if you want the formulas to remain, then remove from the macro the 2 lines that say ".Value = .Value"

The macro:

Sub populate_amounts()
  Dim nRow As Long, lr As Long
  Dim lRow As Long, lTot As Long
 
  Application.ScreenUpdating = False
 
  nRow = Range("A:A").Find("TOTAL", , xlValues, xlWhole, , , False).Row
  lr = Range("H" & Rows.Count).End(3).Row + 1
  lRow = IIf(Range("H" & lr - 1).Value = "TOTAL", lr - 1, lr)
  lTot = lRow + 1
 
  Range("A2").Copy
  Range("H" & lRow).PasteSpecial xlPasteFormats
  Range("C2").Copy
  Range("I" & lRow).Resize(2, 3).PasteSpecial xlPasteFormats
 
  Range("H" & lRow).Value = Range("A" & nRow - 1).Value
  Range("I" & lRow).Value = Range("E" & nRow).Value
  Range("F" & nRow - 1).Copy Range("J" & lRow)
  With Range("K" & lRow)
    .Formula = "=I" & lRow & "+J" & lRow
    .Value = .Value
  End With
  
  Range("A" & nRow).Copy Range("H" & lTot)
  With Range("I" & lTot & ":K" & lTot)
    .Formula = "=SUM(I2:I" & lRow & ")"
    .Value = .Value
    .Font.Bold = True
  End With

  Application.ScreenUpdating = True
End Sub

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

 

[Mussala]

Perfect !

one row will be incremented in columns H to K.

umm,
I would ask if it's possible to avoid repeating to the bottom for the same date when run the macro every time.
if you have better idea to do that I'm all my ears.
if you don't have any idea. I have idea.
I just suggest to use DATE(TODAY) as condition
so if I have dates in column H 05/09/2024 , 06/09/2024, 07/09/2024
then should ignore data for dates 05/09/2024 , 06/09/2024 because depends on DATE(TODAY)
and will replace data for date 07/09/2024.
if you see it's hard to do that or impossible , just forget it .
also may you make bold for TOTAL word in last row ,please?

 

[DanteAmor]

I would ask if it's possible to avoid repeating to the bottom for the same date when run the macro every time.

Everything is possible (or almost everything), if you explain it from the beginning.

In your second example, what you do is put a second record (20/06/2023) after the first record (19/06/2023), by the way, that date is very far from DATE(TODAY). So, continuing with your second example, what you do is put a second record.

so if I have dates in column H 05/09/2024 , 06/09/2024, 07/09/2024
then should ignore data for dates 05/09/2024 , 06/09/2024 because depends on DATE(TODAY)
and will replace data for date 07/09/2024.

I highlighted "and will replace data for date 07/09/2024", because it seems like a good idea, but it would be more helpful if you wrote it in your OP.

also may you make bold for TOTAL word in last row ,please?

Returning to your examples, the word "TOTAL" that you have in column A is in BOLD, and that format is what the macro takes for the "TOTAL" in the last row.

I just suggest to use DATE(TODAY) as condition

Taking your suggestion, try the following macro:

Sub populate_amounts()
  Dim nRow As Long, lr As Long
  Dim lRow As Long, lTot As Long
  Dim f As Range
  Dim nToday As Date
  
  Application.ScreenUpdating = False
  
  nRow = Range("A:A").Find("TOTAL", , xlValues, xlWhole, , , False).Row
  nToday = Range("A2").Value
  
  If nToday <> Date Then
    MsgBox "The date does not correspond to today"
    Exit Sub
  End If
  
  lr = Range("H" & Rows.Count).End(3).Row + 1
  Set f = Range("H:H").Find(nToday, , xlValues, xlWhole)
  If Not f Is Nothing Then
    lRow = f.Row
    lTot = lr - 1
  Else
    lRow = IIf(Range("H" & lr - 1).Value = "TOTAL", lr - 1, lr)
    lTot = lRow + 1
  End If
  
  Range("A2").Copy
  Range("H" & lRow).PasteSpecial xlPasteFormats
  Range("C2").Copy
  Range("I" & lRow).Resize(2, 3).PasteSpecial xlPasteFormats
  
  Range("H" & lRow).Value = Range("A" & nRow - 1).Value
  Range("I" & lRow).Value = Range("E" & nRow).Value
  Range("F" & nRow - 1).Copy Range("J" & lRow)
  With Range("K" & lRow)
    .Formula = "=I" & lRow & "+J" & lRow
    .Value = .Value
  End With
    
  Range("A" & nRow).Copy Range("H" & lTot)
  With Range("I" & lTot & ":K" & lTot)
    .Formula = "=SUM(I2:I" & lRow & ")"
    .Value = .Value
    .Offset(0, -1).Resize(1, 4).Font.Bold = True
  End With

  Application.ScreenUpdating = True
End Sub

 

[Mussala]

thanks again and sorry I don't mentioned from the beginning.
your code works as I would but when DATE (TODAY) is in column A should add in column H:K
based on the code will not add .

 

[DanteAmor]

your code works as I would but when DATE (TODAY) is in column A should add in column H:K
based on the code will not add .

Did you try the code?
For me it works, if today's date exists it updates the values and also updates the Total.
If it doesn't exist, then it adds a new row, and of course also updates the Total row.

Before the macro:

After the macro:

You can see that the macro added the row with today (09/07/2024).
And it updates the totals and also puts them in bold.

 

[Mussala]

sorry it was 2023 year , not 2024!
your code works excellently .
many thanks.