Insert Rows in excel VBA

spycein

Board Regular
Joined
Mar 8, 2014
Messages
68
Hi Everyone,

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

DateInvoicePartyAmountTaxGross
User definedUser definedUser definedUser definedformulaformula

<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

Board Regular
Joined
Mar 30, 2016
Messages
67
Not sure if I totally get it, but maybe this will work:

Code:
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.
 

Forum statistics

Threads
1,085,589
Messages
5,384,632
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top