Excel VBA to Insert new row as Text format into Protected Sheet1 from Sheet2 User updated

Hi All,

Thank you for taking the time to review this thread! I have been searching for three days for a way to accomplish my need and I have not been able to figure this one out.

Setup:
Sheet 1 is Protected to prevent the user from manipulate the data and cause errors. Sheet 1 has existing VBA for upload into a database. Sheet 1 Data does not begin until Row 10 Column C.

Sheet 2 is Unprotected for user update. Users will be allowed to change existing data or add new data to this sheet. The amount of columns used and the column headings will never change. The amount of Rows would be Dynamic since the user can input New Rows.


Primary Goal: Data Created or changed in Sheet 2 should be Inserted or updated in the Protected Sheet 1 without allowing the User to change Sheet 1 directly. The change should be enacted upon click of a command button or automatic. (Preferably upon the click of the command button.)

I have found the following Code but have had issues making both work with what my need is or at all.

This Code I was not able to Run. I received 2 separate Error codes: "sub or function not defined" & "invalid outside procedure"

Private Sub copycolumns()
Dim lastrow As Long, erow As Long

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination = Worksheet("Sheet2").Cells(erow, 1)

Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination = Worksheet("Sheet2").Cells(erow, 2)
Sheet1.Cells(i, 4).Copy
Sheet1.Paste Destination = Worksheet("Sheet2").Cells(erow, 3)

Next i

Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range("A1").Select

End Sub

This code has potential I would just need to be able to expand the columns copied From B:F until the Row is Null and would need to allow the user to place an "X" in Column A to Define Changes or New data to copy to Sheet 1.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("D4").Address Then

        ' Get the last row on our destination sheet (using Sheet2, col A here)...
        Dim intLastRow As Long
        intLastRow = Sheet2.Cells(Sheet2.Rows.Count, "B").End(xlUp).Row

        ' Add our value to the next row...
        Sheet2.Cells(intLastRow + 1, "B") = Target.Value

    End If

End Sub

Thanks again for reviewing. I really need the help!

Hi,

It doesn't appear the code you posted unprotects Sheet1. Before you run anything on Sheet1, it would need to be unprotected and then you can protect it again after the code has run. The code below is how VBA is used to protect/unprotect a password protected sheet. If the sheet doesn't have a password, just drop the "Password" part:

Sheets(1).Unprotect Password:="password"

Hope that provides some guidance,

Dan