how to insert row and copy down formula in a Protected Sheet

[AndrewMartin]

Hi, first post on here so please excuse! I am, and have been for quite some time, trying to set up a spreadsheet to be used by a wide range of users (many with very little excel knowledge) which has a great deal of code and formula within it. I have therefore password protected the sheet to avoid issues with people deleting the formulas etc. However, the sheet I need help with is one where I want around 25 rows to be available to edit for normal use, but, on occasion, people will need additional rows (on some schemes up to 150 rows), so I need an easy way for people to add rows and copy down the formula from the row above, without the sheet being unprotected.

I have tried setting up a macro to add a row and this appeared to work great as it was unlocking the sheet, carrying out the necessary inserting and copying, and then protecting the sheet again (albeit with no password but beggars cant be choosers eh?). The problem with this in practice was that the macro always added the row to say row 26, so if you had added 10no. rows and filled these in and then needed more rows, by clicking the macro button it added the new row in above completed rows, ruining the sequencing.

I then tried setting up groups of 20no. rows and minimising these, therefore only a single row was visible for each group at the bottom of the sheet, and could be expanded when needed, however when the sheet is locked the 'expand' function was also locked and I cant see how to get this aspect unlocked without unprotecting!

So, what I need is something to either allow the 'group' command to be available for use when the sheet is protected, or something else to carry out the function I need that you wonderfully clever people out there know about!

Many thanks!

Andrew

 

[AndrewMartin]

Many thanks to all above - really appreciate the input and advise! I have been able to get this to work without the password and with a button so going well - a couple of issues if I can take up more of your time?


1. The code does what I initially asked perfectly, only thing I didn't mention was that there is additional data in cells below the row I wish to add and the code therefore overwrites this as it stands (i.e. the row I wish to add below is row 35 but within rows 38-45 I have information on how the sheet is compiled which disappears when the code runs). Is there a way when the rows are added that these are 'inserted' rather than adding to the existing rows below?


2. When I add the password element the code does not run and I get the following error message:
Run Time Error '1004':
The password you supplied is not correct. Verify that CAPS LOCK key is off and be sure to use the correct capitalization.

When I click on debug the VBA opens and highlights the line:

Sheet1.Unprotect "Password1"

This is the password used and the capitals are correct (have even tried changing this password on both the sheet and code but no luck).

Any ideas?

Thanks very much!

 

[Sharid]

I can't help on the first one as my coding is limited, as for your second point. The code should work, I can only assume that you must have ran the code with my password in "Password" at least once and the changed it to yours "Password1". However the sheet is still locked under my password and won't accept yours.

Try going to your excel ribbon then Review > Unprotect Sheet and input my password to unlock and then relock with your Password, which was Password1

That should fix it, as the code does work with the password as show on my download link in a previous post.

Sorry i can't help more

 

[Sharid]

You could try this a simple copy from a range and paste into a range

Copies range A2:CG2 and Pastes in row 10,11 and 12 of the same sheet

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False
'Range to copy
Range("A2:CG2").Copy
'Change ranges to paste
Range("A10:A11:A12").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

 

[MARK858]

For #1 depending exactly what you want try either....

Private Sub new_rows()
    'Change your password here
    Sheet1.Unprotect "Password1"
    Dim Rng As Long, i As Long
    Rng = Application.InputBox("Enter number of rows required.", Type:=1)

        For i = 1 To Rng
            'Change source row and sheet name
            Sheet1.Range("a35").EntireRow.Copy
            Sheets("CE REGISTER").Rows(35).Offset(1).Insert Shift:=xlDown
        Next i
    
    Application.CutCopyMode = False
    'Change your password here
    Sheet1.Protect "Password1"
End Sub

or

Private Sub new_rows2()
    'Change your password here
    Sheet1.Unprotect "Password1"
    Dim Rng As Long
    Rng = Application.InputBox("Enter number of rows required.", Type:=1)

            'Change source row and sheet name
            Sheet1.Range("a35").EntireRow.Copy
            Sheets("CE REGISTER").Rows(35).Offset(1).Resize(Rng).Insert Shift:=xlDown
    
     Application.CutCopyMode = False
    'Change your password here
    Sheet1.Protect "Password1"
End Sub

 

[AndrewMartin]

Many thanks to everyone - finally able to get everything working exactly as desired! Had to do a bit of harvesting from a couple of the options above and ended up with (for my sheet entitled CE REGISTER):

Sub Button1_Click()
'Change your password here
Sheets("CE REGISTER").Unprotect "EANER_ASM_52-56"

Dim Rng As Long, i As Long
Rng = Application.InputBox("Enter number of rows required.", Type:=1)
For i = 1 To Rng
'Change source row and sheet name
Range("a35").EntireRow.Copy
Sheets("CE REGISTER").Range("a65536").End(xlUp).Offset(1).Insert Shift:=xlDown
Next i

Application.CutCopyMode = False
'Change your password here
Sheets("CE REGISTER").Protect "EANER_ASM_52-56"

End Sub

Thanks again to all who helped - this has been driving me mad for some time so glad I turned to you!

Andrew