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]

My original question in post2 asked which column has a value in every row. VBA can use that to determine what to copy and where to copy it to
Is there a column that has a value in every used row? If so which column?
Thanks

Yes, but there is a number of columns which have protected information in a number rows and then there is empty comumns where the user enters in another value which is totalled onto the protected data that is not changed.

 

[Yongle]

Which column has a value in EVERY row?
Thanks

 

[trish123]

Which column has a value in EVERY row?
Thanks

Column C,E,F,H,J,L,N,P,R,T,V,X,Z,AB,AD,AE
With Headings this Starts at row 6.

The initial code i was testing worked, however it would place the new rows at the top of the sheet rather than under the last Row where is where i wish it to be.

 

[Yongle]

Try this:

Private Sub CommandButton21_Click()
    Dim Rng As Long, i As Long, cel As Range
        With Sheets("2 .Pricing Sheet")
            .Unprotect "Password"                   'Change your password here
            Set cel = .Range("C" & Rows.Count).End(xlUp).Offset(1)
            Rng = Application.InputBox("Enter number of rows required.", Type:=1)
            .Range("A205").EntireRow.Copy .Range("A" & cel.Row).Resize(Rng)
            Application.CutCopyMode = False
            .Protect "Password"                     'Change your password here
        End With
End Sub

This code pastes the required number of rows below the last row
The last row is determined by the last found cell with a value in column C

 

[trish123]

OMG Yongle You are a genius, But i have another issue, while it is inserting the rows perfectly it is overwriting my total columns whihc i know i probably didnt mention, Is there a way of as well as inserting the extra rows but also keeping the total columns at the end there is a blank line where the sheet ends and where the total of each line begins.

 

[Yongle]

I do not properly understand what you want, but I am sure it will be possible

Which is(are) the "total" column(s)?

At the moment the code copies the whole of row 205
- should it only be pasting some of those columns?

 

[trish123]

The "total" Row would start Row 207, and it basically is the sum total of each of the column in the entry cells which i am pasting, but i wish to also total the cells of the inserted rows also.

 

[Yongle]

You are not making this easy..

Let's use a real example using row numbers looking at your worksheet as it currently stands

You want to paste 5 rows. Where to - which row numbers? (please do not say the end of the data - that will not help at all)

thanks

 

[trish123]

You are not making this easy..

Let's use a real example using row numbers looking at your worksheet as it currently stands

You want to paste 5 rows. Where to - which row numbers? (please do not say the end of the data - that will not help at all)

thanks

I Do apologise probably not the best at explaining
So what i want

Lets say there is 5 rows with information each with a total cell at row 7 which sums the values of the specified cells in the sheet

User has opted to enter 5 extra rows to sheet to insert data,

These extra rows will be inserted into the last row with information allocated in this case row 5

So now there is 10 rows

what i wish now is that the total row is now moved down in this case to row 12 and not over written and to take into the consideration the values which is inserted into the new rows.

I really hope this makes since

 

[Yongle]

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