Sorry for taking so long... Change G2 to G9

lngLastRow = range("G9").End(xlDown) + 1

--
HTH...

Jim Thomlinson


"Lisa" wrote:

It looks like it is going to work but not putting the row in the right place
still. The table i require the row to be added too doesnt start until row 9 -
headers row and when i press the macro button with the below its adding the
new row into row 1

Lisa

"Jim Thomlinson" wrote:

So Column G has formulas in it and we can rely on that to always be filled
in. We can now leverage the End(xlDown) feature to get the last row.

So something like this should do
'******************
Dim lngLastRow as Long 'variable to hold last row number

lngLastRow = range("G2").End(xlDown) + 1

Rows(lngLastRow ).Select
Selection.Insert Shift:=xlDown
Range("G" & lngLastRow ).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.15-RC[-1])"
Range("H" & lngLastRow ).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("C" & lngLastRow, "E" & lngLastRow ).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A" & lngLastRow, "E" & lngLastRow).Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A" & lngLastRow).Select
'*********************
--
HTH...

Jim Thomlinson


"Lisa" wrote:

Rows("23:23").Select
Selection.Insert Shift:=xlDown
Range("G23").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.15-RC[-1])"
Range("H23").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("C23:E23").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A23:E23").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A23").Select
End Sub
This macro keeps adding the row into row 23 but i want it to add at the
bottom of my table titled shopping cart. All the formulas and formatting have
copied correctly!

"Jim Thomlinson" wrote:

Try recordinga macro to see what you get. Read the macro over to see what it
is doing and then post back with the code and a description of the required
changes.

You will probably be surprised with how close you can get.
--
HTH...

Jim Thomlinson


"Lisa" wrote:

I currently have a worksheet with a number of tables in it. I want to add a
macro button that will add a row at the bottom of a specific table - Shopping
carts. I also want the row to have copied all the formulas and cell
formattings from the above row. I have found a thread on here that explains
what formula to add but it looks very complicated i need the 'How To' broken
down into a step by step version!