Insert Row

[rachitm]

Hi,

I have seen the mvp link to insert row. But I have a few different
needs. On my excel sheet, I have 2 tables, TABLE1 and TABLE2. I need to
place 2 different command buttons, one for each table, which can insert
rows on each table respectively. So when I press the button for TABLE1,
it inserts a row below Row1, it copies the format from Row1,and pastes
the format from Row1 to the inserted row (Row2). If I press it again,
it inserts a row below Row2, it copies the format from Row1 again and
pastes on the inserted row (Row3). I dont want to insert a row below
where my curson is on the sheet, I want it to insert a row below the
last used row in each table. Also, the reason I need it to insert a row
everytime is so that Table 2 pushes itself down everytime the macro
button is pressed for TABLE1:

TABLE 1
Row 1 Data
--
--
--

TABLE 2
Row 1 Data
--
--
--



Thanks, Please let me know if anyone has any questions regarding the
explanation above.

 

[Jef Gorbach]

Think this does what you want: find the blank row between the two tables
then add a blank row to each, formated to match the rest of the rows.

Sub Macro1()
'process for table one
'goto table's last row, presuming there is a blank row between the two
tables
Cells(65536, ActiveCell.Column).End(xlUp).End(xlUp).End(xlUp).Activate
'insert a blank row below it
Rows(ActiveCell.Row + 1).Insert
'personally I prefer specifing the format over copying it
With Rows(ActiveCell.Row + 1)
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 12
.HorizontalAlignment = xlRight
End With
'repeat for table two
Cells(65536, ActiveCell.Column).End(xlUp).Activate
Rows(ActiveCell.Row + 1).Insert
With Rows(ActiveCell.Row + 1)
.Font.Bold = True
.Font.Name = "Arial"
.Font.Size = 12
.HorizontalAlignment = xlRight
End With
'then go home
Cells(1, 1).Activate
End Sub

 

[rachitm]

Hi,

Thank You for your help with this. but it didnt quite work that well
with the code above. I have sent you the document that i am working on
via email. I may not have explained things quite as well as I should
have.

If you get time, in that document I have 3 tables, but I am only
concerned with table 1 and table 2. The reason I want to copy rather
than define the format is because it also has formulas in them that
should be copied to the inserted row. And I have 2 Insert Row buttons,
one for each table. And everytime I insert a row it needs to push all
the data below it down to make space for the inserted row.

Thanks a lot for your help. Sent you the doc on your email address.