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.

[TABLE="class: grid, width: 476"]
<colgroup><col span="4"><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Invoice[/TD]
[TD]Party[/TD]
[TD]Amount[/TD]
[TD]Tax[/TD]
[TD]Gross[/TD]
[/TR]
[TR]
[TD]User defined[/TD]
[TD]User defined[/TD]
[TD]User defined[/TD]
[TD]User defined[/TD]
[TD]formula[/TD]
[TD]formula[/TD]
[/TR]
</tbody>[/TABLE]

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.