Simple VBA to add row and fill down

OneTimeUser

New Member
Joined
Apr 16, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi Experts,
I want to be able to click a button in my worksheet and tell excel to copy the data/formulas/formatting to a new line, inserted directly below my currently selected line. However I only want the data/values that are held in the row from cells J:AB to be filled down, as the other fields are variable.

So the code needs to say:
1. Add new line in table below currently selected row (not at the end of the table)
2. New line must retain all formatting and formulas from currently selected row
3. Fill down all data for columns J:AB to the new line from currently selected row

I have never done any sort of coding and have just been dabbling in what I can self teach through forums, so if anyone would be so kind as to no only help with the code, but explain as it goes along I would be forever grateful to you!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,532
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

So you want to copy down the values/formulas located in columns J:AB, but want to copy down the formatting for ALL columns?
So how many columns are there in the table total (what are the starting/ending columns?
Is it really set up as a table, or is it just a list?
If you insert a row in the middle of a table in Excel, the formula and formatting should automatically be copied to that new row.
 

OneTimeUser

New Member
Joined
Apr 16, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi Joe4,
Yes there are 30 columns in the table (A:AD) Some of these are hidden if it matters?. Formatting and formulas need to stay the same across these columns, and then the data entered in cells J:AB copied down.

Yes of course - silly me - I forgot about the automatic table formatting, so I guess that part is sorted already. It is definitely a table.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,532
Office Version
  1. 365
Platform
  1. Windows
Try this code to insert a blank row in your table below the active cell:
VBA Code:
Sub InsertTableRowBelow()
    Rows(ActiveCell.Row + 1).Insert shift:=xlDown
End Sub
and then let us know if you automated table formatting/formulas takes care of the rest, or if there is still something that you need done.
 

OneTimeUser

New Member
Joined
Apr 16, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Morning Joe,
The code works perfectly for the new row in the table (formulas / formatting) thank you, but doesn't bring down the information held in the cells (ie customer information). This is the info I need to fill down from J:AB.
The customer data only needs to be copied down to the new line, not any further. Hopefully that makes sense
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,532
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
Sub InsertTableRowBelow()
    Dim r As Long
    r = ActiveCell.Row
    Rows(r + 1).Insert shift:=xlDown
    Range(Cells(r, "J"), Cells(r, "AB")).Copy Cells(r + 1, "J")
End Sub
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,532
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,803
Messages
5,655,391
Members
418,195
Latest member
LabraLime

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