How to create a new row in a table and drag formulas down with a "button"

[Wave27]

I am working on a personal budget file for my finances and I thought it would be helpful to assign a macro to a "button" that would make repetitive tasks easier. I would like to add a row to the bottom of the table when needed and have the above formulas copy down from into the new row. I know this automatically occurs when you have 3 or more cells with the same formula, but i can't seem to make it work when the formula changes references.

I am new to macros and VBA coding. From some online research I found some code that allows me to insert a new row to the end of my table (B9:E24) with the click of a "button", but I can't get it to autofill the formula in the "Balance" column (E) to the new row. I tried recording my own macro, but the autofill function doesn't seem to work because it requires a "range" of cells, which changes every time i click the "button" to add a new row. Is there some code I can add to insert a new row to the end of my table AND have the formulas copy down into that new row? Any help would be greatly appreciated.

Book3.xlsm
	A	B	C	D	E	F
2
3
4		PERSONAL CHECKING ACCOUNT
5
6		Statement Period
7		Beginning Balance		$ 15,862.39
8		Ending Balance		$ 4,236.19
9		Date	Source	Amount	Balance
10		11/14/22	credit card payment	$ (12,820.40)	$ 3,041.99
11		11/21/22	Venmo	$ (319.87)	$ 2,722.12
12		11/22/22	paycheck	$ 1,000.00	$ 3,722.12
13		11/22/22	Venmo refund	$ 319.87	$ 4,041.99
14		11/22/22	Venmo	$ (319.87)	$ 3,722.12
15		11/23/22	Transfer to tax account	$ (300.00)	$ 3,422.12
16		12/5/22	auto insurance	$ (125.97)	$ 3,296.15
17		12/7/22	paycheck	$ 1,000.00	$ 4,296.15
18		12/8/22	Interest payment	$ 0.04	$ 4,296.19
19		12/9/22	gas	$ (10.00)	$ 4,286.19
20		12/10/22	gas	$ (10.00)	$ 4,276.19
21		12/11/22	gas	$ (10.00)	$ 4,266.19
22		12/12/22	gas	$ (10.00)	$ 4,256.19
23		12/13/22	gas	$ (10.00)	$ 4,246.19
24		12/14/22	gas	$ (10.00)	$ 4,236.19
25		Beginning Balance		$15,862.39
26		Deposits & Additions		4	$2,319.91
27		Withdrawals & Subtractions		11	$(13,946.11)
28		Ending Balance		$4,236.19
29
30
31
32
template 1

Cell Formulas
Range	Formula
E10	E10	=IF(ISBLANK(D10),"",$D$7+D10)
E11:E24	E11	=IF(ISBLANK(D11),"",E10+D11)
D25	D25	=D7
D26	D26	=COUNTIF(D10:D24,">0")
E26	E26	=SUMIF(D10:D24,">0")
D27	D27	=COUNTIF(D10:D24,"<0")
E27	E27	=SUMIF(D10:D24,"<0")
D28	D28	=SUM(D25,E26,E27)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D27	Expression	=IF($E$27<=0,TRUE,"")	text	YES
D28:E28	Cell Value	<>$D$8	text	NO
B27:C27	Expression	=IF($E$27<=0,TRUE,"")	text	NO
E27	Cell Value	<0	text	NO
B10:B24	Expression	=IF(D10>0,TRUE,"")	text	NO
B10:B24	Expression	=IF(D10<0,TRUE,"")	text	NO
C10:C24	Expression	=IF(D10>0,TRUE,"")	text	NO
C10:C24	Expression	=IF(D10<0,TRUE,"")	text	YES
D10:D24	Cell Value	>0	text	NO
D10:D24	Cell Value	<0	text	NO

 

[Wave27]

Sub Insert()
Dim the_sheet As Worksheet
Dim table_list_object_row As ListObject
Dim table_object__row As ListRow
Set the_sheet = Sheets("template 1")
Set Table_list_object = the_sheet.ListObjects(1)
Set table_object_row = Table_list_object.ListRows.Add

table_object_row.Range(1, 4).Formula = ""


End Sub


Here is the macro I created. This only allows me to insert a new row to the end of my table

 

[MidNiteRaver]

I have a similar issue. I gat to insert a row but no formulas are copied and the totals are nut updated to reflect the new sum ranges. I even tried ChatGPT and though it came close the issue with not carrying over the formulas and updating totals were never solved

 

[Alex Blakenburg]

I have a similar issue. I gat to insert a row but no formulas are copied and the totals are nut updated to reflect the new sum ranges. I even tried ChatGPT and though it came close the issue with not carrying over the formulas and updating totals were never solved

Any reason you don't use an Excel Table ? It will do all that for you automatically.
See:

Save Time With Excel Tables

Excel Tables are a must have skill for all Excel users and their Structured References are a much simpler way to dynamically name ranges.

www.myonlinetraininghub.com

 

[Dave Patton]

Excel Table and one formula
A2:C2 use Centre across selection; this does not show on post

T202404.xlsm
	A	B	C
1
2	PERSONAL CHECKING ACCOUNT
3		Balance	4,236.19
4
5	Date	Source	Amount
6	01-01-22	opening	15,862.39
7	01-01-22	credit card payment	(12,820.40)
8	01-01-22	Venmo	(319.87)
9	01-01-22	paycheck	1,000.00
10	01-01-22	Venmo refund	319.87
11	01-01-22	Venmo	(319.87)
12	01-01-22	Transfer to tax account	(300.00)
13	01-01-22	auto insurance	(125.97)
14	01-01-22	paycheck	1,000.00
15	01-01-22	Interest payment	0.04
16	01-01-22	gas	(10.00)
17	01-02-22	gas	(10.00)
18	01-03-22	gas	(10.00)
19	01-04-22	gas	(10.00)
20	01-05-22	gas	(10.00)
21	01-06-22	gas	(10.00)
4a

Cell Formulas
Range	Formula
C3	C3	=SUBTOTAL(109,Table4[Amount])