adding a row on a sheet that has multiple sections

[lillypop]

Hello,

I need to add a row each month at the bottom of other rows accumulated from previous months, with the same format and some formulas from the row above. (eg. area B2:BB100).

I firstly tried to detect the last active cell in column B, and copy and paste a row underneath it, but because there is another table underneath, it keeps on adding a row below that last table on the sheet. Now I try to define the first table (e.g. from B2:BB100 = "Table1"), and add a row below it, shift down, but the defined area is always fixed, which is not what I want, because the table area should increase each month. Any idea?



Sub addrow()

Dim NewRow as Range

With Range("Table1").EntireRow(Range("Table1").Rows.Count).Offset(0,0)
.EntireRow.Copy

With.Offset(1,0).EntireRow
.PasteSpecial XlPasteFormats
.PasteSpecial XlPasteFormulas

on Error Resume Next
.SpecialCells(xlCellTypeCOnstants).ClearContents
On Error Goto 0

End With
End With

End sub

 

[igold]

If you want to add a row to the bottom of Table1, you may want to try this line of code. It will automatically pick up the formulas and formats.

[COLOR=#2D2C2A][FONT=Verdana]ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:= True[/FONT][/COLOR]