Allow user to enter a new row with replicated data in a protected sheet

[trish123]

Hi All,

i am not the best at this coding but i do try, I have been playing around with the folllowing code which works but not the way i wish, it pastes the data into the top of the workbook instead of the bottom. Is there a way of allowing the user to click on the button and then paste the required range into the end of the cells to add onto the cells above it?

Private Sub CommandButton21_Click()

'Change your password here
Sheets("2 .Pricing Sheet").Unprotect "Password"

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("a205").EntireRow.Copy
Sheets("2 .Pricing Sheet_In Term").Range("a65536").End(xlUp).Offset(1).Insert Shift:=xlDown
Next i

Application.CutCopyMode = False
'Change your password here
Sheets("2 .Pricing Sheet").Protect "Password"

End Sub

 

[trish123]

this bit makes sense:
There are 5 rows
Row 6 is empty
Row7 has totals (formula should sum rows1 to 6 -to allow formula to auto-stretch)
5 rows inserted (6,7,8,9,10) - which moves the total to row12
Row 11 is now empty

But this doesn't:
- your original code copied from row 205
- after inserting 5 rows it is now row 210
- the next time the code runs it will copy what was previously row 200

Which row do you want to copy?
1. a model row which starts life in row 205 (and slips down the workbook as we add more rows) ? - we can use a Named Range and then does not matter which row it is in
2. a model row saved in a hidden sheet (row number never changes) ?
3. a row from above the total (with its values removed and formulas intact) ?

After we have sorted this, remind me tell you about Excel Tables!

thanks

Thank you so much Yongle i have really learned so much and thank you for taking the time, it is really appreciated.

So going on you above explanation regarding "which row to copy" All 3 sounds good, however, if the user has already inserted values into row 205 these will copy down, i would be hoping that by the user pressing the button to insert a number of rows that the specific rows that require the information would be blank. so i am thinking option 2 or 3 would be the best option.

 

[Yongle]

Let's go with option 3
- which columns contain formulas?

 

[Yongle]

Here you go...
Row 7 is copied each time
Totals formula range must include the blank row above the total
Column C must contain a value in the totals row for this to work
- amend this otherwise to a column that does contain a value in the totals row

r = .Cells(Rows.Count, "[COLOR=#ff0000]C[/COLOR]").End(xlUp).Row - 1

Private Sub CommandButton21_Click()
    Dim Rng As Long, c As Long, r As Long, pRng As Range
    On Error Resume Next
        Rng = Application.InputBox("Enter number of rows required.", Type:=1)
    If Err.Number > 0 Then GoTo TheEnd:
        With Sheets("2 .Pricing Sheet")
            .Unprotect "Password"     'Change your password here
            r = .Cells(Rows.Count, "C").End(xlUp).Row - 1                           'next row
            c = .Cells(7, Columns.Count).End(xlToLeft).Column                       'last col
            .Rows(r).Resize(Rng).Insert                                             'insert rows
            Set pRng = .Cells(r, "A").Resize(Rng)                                   'determine paste range
            .Cells(7, "A").Resize(, c).Copy pRng                                    'copy row 7 & paste
            Application.CutCopyMode = False
            pRng.Resize(, c).SpecialCells(xlCellTypeConstants).ClearContents        'remove all except formulas
            .Protect "Password"       'Change your password here
        End With
TheEnd:
End Sub

 

[trish123]

That works a dream, but now i have another prediciment, How am i to get the totals at the at the bottom of the sheet to include the values from the new rows.

 

[Yongle]

Make sure that the totals formula includes the blank row above the total in the range
When rows are inserted they go above the blank row and formulas will update correctly automatically

example
if data in A6 to A10
blank row A11
formula
=SUM(A6:A11)

 

[trish123]

Hi Yongle,

Yep i have done as you have said and the empty row above but the totals row but the formulas are still not updating

 

[Yongle]

- this is standard stuff and should not give a different result
- if a row is inserted inside the range referred to in a formula then the range expands

Please post one of the formulas that is refusing to update and also let me know which cell it is sitting in

thanks

 

[trish123]

a Sum formula =SUM(K6:K29) in cell K31 and other sum formulas in the following Cells Q31, W31, AC31 and AH31 non of which are updating.

 

[Yongle]

This is your problem
a Sum formula =SUM(K6:K29) in cell K31

Replace with
a Sum formula =SUM(K6:K30) in cell K31

 

[trish123]

Thanks for that such a silly mistake, works absolutely perfect you are an absolute genius Yongle, really appreciate your help.