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

 

[Yongle]

Is there a value in column A in every row?
If not, which column best determines the last used row?

 

[Yongle]

Ty this
- place the cursor below the last row with data when asked to select row

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

    Dim Rng As Long, i As Long, cel As Range
    Set cel = Application.InputBox("select row", , , , , , , 8)
    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
        Range("A" & cel.Row).Insert Shift:=xlDown
    Next i

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

End Sub

Instead of looping

    For i = 1 To Rng
'Change source row and sheet name
        Range("a205").EntireRow.Copy
        Range("A" & cel.Row).Insert Shift:=xlDown
    Next i

Try this

  Range("a205").EntireRow.Copy
  Range("A" & cel.Row).EntireRow.Resize(Rng).Insert Shift:=xlDown

 

[trish123]

Is there a value in column A in every row?
If not, which column best determines the last used row?

Hi

The Last row at the moment is row A205, i wish to allow the user if required to enter a number of rows if required below this row and to paste the formulas and number sequence from the the row above.

is this possible?

 

[Yongle]

try code in post3

 

[trish123]

The above code enters new empty rows, but what i wish to do is it to also replicate the cell above which has formulas etc and compy this into the new inserted rows of possible

 

[trish123]

The above code enters new empty rows, but what i wish to do is it to also replicate the cell above which has formulas etc and compy this into the new inserted rows of possible

This is all in the one sheet

 

[Yongle]

The above code enters new empty rows, but what i wish to do is it to also replicate the cell above which has formulas etc and compy this into the new inserted rows of possible

Not for me. It copies and pastes everything.
Try it again. Did you paste in my whole code or only what you spotted had changed?

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

    Dim Rng As Long, i As Long, cel As Range
    Set cel = Application.InputBox("select row", , , , , , , 8)
    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
        Range("A" & cel.Row).Insert Shift:=xlDown
    Next i

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

 

[trish123]

Not for me. It copies and pastes everything.
Try it again. Did you paste in my whole code or only what you spotted had changed?

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

    Dim Rng As Long, i As Long, cel As Range
    Set cel = Application.InputBox("select row", , , , , , , 8)
    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
        Range("A" & cel.Row).Insert Shift:=xlDown
    Next i

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

Yes, i have used the above code is there another way of doing this instead of choosing the row. Again maybe i am doing this incorrectly but all it does is give me empty rows. Again i am just a beginner. I wish to allow the user to click on the My Button as below below Cell 5 and replicate the information that is in the cell above in this case row 5 to the defined number of cells as stipulated by the user in the message box, so to read into the total figures.

1	--Select Area by Drop Down by Clicking Here--		€15.0000	€1.2900
2	--Select Area by Drop Down by Clicking Here--		€15.0000	€1.2900
3	--Select Area by Drop Down by Clicking Here--		€15.0000	€1.2900
4	--Select Area by Drop Down by Clicking Here--		€15.0000	€1.2900
5	--Select Area by Drop Down by Clicking Here--		€15.0000	€1.2900

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

My BUTTON
Total 75.000 6.4500

 

[Yongle]

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