Add Row not copying formula from cell above

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have a macro that adds a new row to a table. It will copy the formula correctly in column A but it doesnt in column B. I dont know why it is doing this.

VBA Code:
Sub AddRow()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("PayData")
'add a row at the end of the table
tbl.ListRows.Add
'add a row as the fifth row of the table (counts the headers as a row)
'tbl.ListRows.Add 5
End Sub
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Is column B really in your table?
Or perhaps was it added after the fact and actually resides outside of your table?
What are the formulas in columns A and B?
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
The table was built and then the code added. Column B was included from the beginning.

Column A= Just adds 7 days to the date in the cell above
Column B= =SUM(E6+$L$2) this totals the Outstanding Balance from the week before and the Weekly Premium in the Employee Info table
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Can you give us the actual formula that resides in column A too?
And then post a small sample of your data?

I would like to try to recreate this on my side to see if I can reproduce the behavior you are describing.

By the way, you do NOT to use SUM in your formula. Using SUM and + is redundant. You only need one or the other.
So change that formula to:
Excel Formula:
=E6+$L$2

By the way, is your table TRULY a data table in Excel?
I ask because typically your formula would not look like this:
Excel Formula:
=SUM(E6+$L$2)
Table formulas typically use heading/field names instead of range references.
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

=A8+7
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Info.png
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hey I actually fixed it. So apparently when the table was created Column B had a formula. When I created a new formula it was not picking up the changes for the new rows. I changed the table to a range and then back to a table and boom it works! Thank you for the help :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Hey I actually fixed it. So apparently when the table was created Column B had a formula. When I created a new formula it was not picking up the changes for the new rows. I changed the table to a range and then back to a table and boom it works! Thank you for the help
That is kind of what I suspected.
Glad you got it figured out and working!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,764
Members
415,926
Latest member
jerrynababa

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
Top