Solved: Inserting rows through a button

[cleturno]

I know that this is simple, but I jsut wanted to ask questions before I got started to make sure that I am not missign anything. There are three data tables on this sheet. If a user needs more rows they press the button. It copies the formatting from a sheet called data and then it inserts a blank row identical in formatting to the rest of the table and then. pastes in the formatting so the user can fill it in. The reason that I am doing this is becuase I am trying to get to the point where they can lock it down and there is not way that they would ever need to insert anything else into the sheet manually. I know that I am going to need to use dynamic cell references on the tables since the rows above could increase. Just want to make sure that I am correct in assuming this and not missing anything.


Thanks,
Chris

[mdmackillop]

Hi Chris,
I really don't like your use of merged cells in the addition column, but you can insert rows and copy down to achieve your object.
For Personnel

Option Explicit
Option Compare Text

Private Sub CommandButton1_Click()
NewPersonnel
End Sub

Sub NewPersonnel()
Dim Cel As Range
Set Cel = Sheets("Sheet1").Columns(1).Find("Equipment Rental").Offset(-1)
With Cel.Resize(, 15)
.Insert Shift:=xlDown
.Offset(-1).FillDown
.Offset(-1).Resize(, 5).ClearContents
.Offset(-1, 6).Resize(, 5).ClearContents
End With
Cel.Offset(, 11).Formula = "=SUM(L3:M" & Cel.Row - 1 & ")"
End Sub

[cleturno]

I did not to the inital design of the forms and the problem is that there are too many different types of data within this workbook. This almost a 60 sheet workbook and I am not going to go back and redo all the sheets. They have been formatted and rebuilt so many times it is not true. That is only part of the sheet. There are three other data tables above that section. As a matter of preference I would agree with you to do away with the merged cells, but at this point it is easier to live with them at least until they decide to do antoer major rebuild which I am hoping will take everything into VB.NET. Only time will tell though.

Thanks,
Chrsi

[cleturno]

Thanks for the help, this thread is solved