Insert Rows in excel VBA

[spycein]

Hi Everyone,

I have this following table in a protected excel sheet which consist of formulated and user defined cells.

Date	Invoice	Party	Amount	Tax	Gross
User defined	User defined	User defined	User defined	formula	formula

<colgroup><col span="4"><col span="2"></colgroup><tbody>
</tbody>

User defined cells are unprotected and formulated cells are protected so that no one can delete or alter the formula.

I am looking for a VBA code through which i can insert my desire numbers of rows by clicking a tab in the excel excel.

for example, There should be a button on the sheet, when i would click that button a message box should appear asking "how many rows do you want to insert". If i enter 5 then five rows should get inserted in the sheet below the existing table with populated with existing formula from the above row. which means i need to copy the entire above row but the excel sheet should be protected.

Hope i made my points clear.

Thanks

Shib

 

[wesborland1234]

Not sure if I totally get it, but maybe this will work:

Private Sub CommandButton1_Click()
    Dim rows As Integer
    Dim lastRow As Long
    Dim i As Integer
    
    rows = textbox1.Text
    
    lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Cells(lastRow, 1).EntireRow.Copy
    
    For i = 1 To rows
        Cells(lastRow + i, 1).PasteSpecial
    Next
End Sub

That will take the last row, and copy it a specified number of times.