spycein

Board Regular
Joined
Mar 8, 2014
Messages
106
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,071
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top