Add Row to end of Table with a Macro Button

L

Lisa

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!
 
J

Jim Thomlinson

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.
 
L

Lisa

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!
 
J

Jim Thomlinson

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Gord Dibben

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


Gord Dibben MS Excel MVP
 
L

Lisa

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 said:
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 said:
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 said:
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


:

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!
Click to expand...
Click to expand...
Click to expand...
 
L

Lisa

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 said:
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.
Click to expand...

This is a copy of the thread that i read!

Lisa
 
J

Jim Thomlinson

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

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

--
HTH...

Jim Thomlinson


Lisa said:
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 said:
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 said:
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!

:

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


:

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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Add a Subtotal Row to an Existing Table? 3
Macro to insert row at the end of sheet 7
How do I add a row to a data table? 3
Excel Help to copy information from one row at a time to another sheet 1
Need Macro to autofill formula to last row with data 4
How do I create a macro to duplicate the current row? 3
INSERT NEW ROW MACRO BUTTON 1

Top