Writing macros to add rows to a table

[kec7]

I want to write a macro that will allow me to add rows to a table in Excel. I would like to do this across several tabs. I would like the macro to find the last row in my table with data in it, and then add a row below the last row. After adding the row, I would like it to copy the formulas from the row above it into the new row. I’m not sure how to do this because the last row with data in it will not be on the same place on each sheet.

After I add the row, I would like to make the ranges in a summary formula I have written update to include the new rows that I added. I'm not sure if this is possible, but any help would be greatly appreciated!!

 

[venkat1926]

you need not ADD a row after the lasts row , it is already available

try this macro

Sub test()
Dim r As Range
Set r = Cells(Rows.Count, "A").End(xlUp)
r.EntireRow.Cut r.Offset(1, 0)
  ' should it be cut or copy. this row will be empty if cut is usesd
'for copying whole data to sumary
Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Copy
Worksheets("summary").Range("a1").PasteSpecial
Application.CutCopyMode = False
End Sub

 

[kec7]

Thanks! I ended up using this, which seems to work. Thanks again for your reply.

Sub NewRowMacro()

Cells.Find(What:="Deliverable / Milestone", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


ActiveCell.Offset(2, 0).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop


ActiveCell.EntireRow.Insert Shift = xlDown 'create new row
ActiveCell.Offset(-1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1) 'copy previous row
ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

End Sub