Add Row to end of Table with a Macro Button
 

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!

Add Row to end of Table with a Macro Button
 

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!

Add Row to end of Table with a Macro Button
 

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!

Add Row to end of Table with a Macro Button
 

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!

Add Row to end of Table with a Macro Button
 

Post the complicated formula or a message ID from the thread you found.


Gord Dibben MS Excel MVP

On Fri, 19 Jun 2009 03:11:01 -0700, Lisa <Lisa @discussions.microsoft.com
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!

Add Row to end of Table with a Macro Button
 

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!

Add Row to end of Table with a Macro Button
 

Paste this in a general module and it will work on the active worksheet:

Sub addafterlastF()
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For x = LastRow To 1 Step -1
Cells(x, 1).Select
b = ActiveCell.Text
If b = "F" Then
Cells(x + 1, 1).Select
Selection.Insert Shift:=xlDown
Range("Freehold").Select
Selection.Copy
Cells(x + 1, 1).Select
ActiveSheet.Paste
End If
Next
Cells(1, 3).Select
End Sub

Will that do?

Mike

"Karen McKenzie" wrote:

I want a macro to look in column A, find the last entry "F", then to insert a
row below this and copy all formatting and formulae from named range
"Freehold" into this row, leaving the cursor in column C where user would
start inputting data.


This is a copy of the thread that i read!

Lisa

"Gord Dibben" wrote:

Post the complicated formula or a message ID from the thread you found.


Gord Dibben MS Excel MVP

On Fri, 19 Jun 2009 03:11:01 -0700, Lisa <Lisa @discussions.microsoft.com
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!

Add Row to end of Table with a Macro Button
 

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!