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

Wave27

New Member
Joined
Feb 3, 2023
Messages
2
Office Version
  1. 2019
Platform
  1. MacOS
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
ABCDEF
2
3
4PERSONAL CHECKING ACCOUNT
5
6Statement Period
7Beginning Balance$ 15,862.39
8Ending Balance$ 4,236.19
9DateSourceAmountBalance
1011/14/22credit card payment$ (12,820.40)$ 3,041.99
1111/21/22Venmo$ (319.87)$ 2,722.12
1211/22/22paycheck$ 1,000.00$ 3,722.12
1311/22/22Venmo refund $ 319.87$ 4,041.99
1411/22/22Venmo$ (319.87)$ 3,722.12
1511/23/22Transfer to tax account$ (300.00)$ 3,422.12
1612/5/22auto insurance$ (125.97)$ 3,296.15
1712/7/22paycheck$ 1,000.00$ 4,296.15
1812/8/22Interest payment$ 0.04$ 4,296.19
1912/9/22gas$ (10.00)$ 4,286.19
2012/10/22gas$ (10.00)$ 4,276.19
2112/11/22gas$ (10.00)$ 4,266.19
2212/12/22gas$ (10.00)$ 4,256.19
2312/13/22gas$ (10.00)$ 4,246.19
2412/14/22gas$ (10.00)$ 4,236.19
25Beginning Balance$15,862.39
26Deposits & Additions4$2,319.91
27Withdrawals & Subtractions11$(13,946.11)
28Ending Balance$4,236.19
29
30
31
32
template 1
Cell Formulas
RangeFormula
E10E10=IF(ISBLANK(D10),"",$D$7+D10)
E11:E24E11=IF(ISBLANK(D11),"",E10+D11)
D25D25=D7
D26D26=COUNTIF(D10:D24,">0")
E26E26=SUMIF(D10:D24,">0")
D27D27=COUNTIF(D10:D24,"<0")
E27E27=SUMIF(D10:D24,"<0")
D28D28=SUM(D25,E26,E27)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D27Expression=IF($E$27<=0,TRUE,"")textYES
D28:E28Cell Value<>$D$8textNO
B27:C27Expression=IF($E$27<=0,TRUE,"")textNO
E27Cell Value<0textNO
B10:B24Expression=IF(D10>0,TRUE,"")textNO
B10:B24Expression=IF(D10<0,TRUE,"")textNO
C10:C24Expression=IF(D10>0,TRUE,"")textNO
C10:C24Expression=IF(D10<0,TRUE,"")textYES
D10:D24Cell Value>0textNO
D10:D24Cell Value<0textNO
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
Excel Table and one formula
A2:C2 use Centre across selection; this does not show on post

T202404.xlsm
ABC
1
2PERSONAL CHECKING ACCOUNT
3Balance4,236.19
4
5DateSourceAmount
601-01-22opening15,862.39
701-01-22credit card payment(12,820.40)
801-01-22Venmo(319.87)
901-01-22paycheck1,000.00
1001-01-22Venmo refund 319.87
1101-01-22Venmo(319.87)
1201-01-22Transfer to tax account(300.00)
1301-01-22auto insurance(125.97)
1401-01-22paycheck1,000.00
1501-01-22Interest payment0.04
1601-01-22gas(10.00)
1701-02-22gas(10.00)
1801-03-22gas(10.00)
1901-04-22gas(10.00)
2001-05-22gas(10.00)
2101-06-22gas(10.00)
4a
Cell Formulas
RangeFormula
C3C3=SUBTOTAL(109,Table4[Amount])
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top