Simple VBA to add row and fill down

[OneTimeUser]

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!

 

[Joe4]

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]

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]

Try this code to insert a blank row in your table below the active cell:

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]

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]

Try this:

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

 

[OneTimeUser]

Perfect. Works a treat. Thank you ?

 

[Joe4]

You are welcome.