Using VBA code that will move a row to another sheet based on the selection from a drop-down menu. How to get it to work when protecting the sheet

Phil 20 Reputation points
2025-10-27T16:47:03.8566667+00:00

Hello there,

I'm looking for some help in using VBA code on excel. I have several sheets set up so that when I select from a drop down, some information is copied to other sheets and the line is deleted from the 'Register'.

The problem - I have the sheets protected to prevent certain areas being changed/amended. The users are only able to fill certain areas of the sheets. This VBA code won't work when the sheets are protected. Any help would be greatly appreciated. The current VBA code is:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

Dim rng As Range

Dim lastRow As Long

'Check if the changed cell is in column Z (Status)

If Target.Column = 26 Then

'Get the selected worksheet name

Set ws = Worksheets(Target.Value)

'Get the last row in the selected worksheet

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

'Copy the row to the selected worksheet

Set rng = Range("A" & Target.Row & ":D" & Target.Row)

rng.Copy ws.Range("A" & lastRow)

'Delete the row from the Register sheet

rng.Delete Shift:=xlUp

End If

End Sub

Microsoft 365 and Office | Excel | Other | Windows
0 comments
Answer accepted by question author
IGYQ 22,340 Reputation points Independent Advisor
2025-10-27T17:42:03.5266667+00:00

Hi!

Welcome to Microsoft Q&A forum.

You can do it by temporarily unprotecting the sheet (and the destination sheet, if protected) inside your Worksheet_Change event,and reprotecting them immediately afterward. If this is possible to your work you can study sample below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet

Dim rng As Range

Dim lastRow As Long

Dim pw As String



' Your worksheet password (if any)

pw = "yourpassword"  ' Change this to your actual password, or leave blank if none



On Error GoTo SafeExit

Application.EnableEvents = False



' Only react to changes in column Z (column 26)

If Not Intersect(Target, Me.Columns(26)) Is Nothing Then


    

    ' Unprotect current sheet

    Me.Unprotect Password:=pw


    

    ' Get the destination sheet

    Set ws = Worksheets(Target.Value)


    

    ' Unprotect destination sheet if it’s protected

    ws.Unprotect Password:=pw


    

    ' Find the next available row in the destination sheet

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1


    

    ' Copy the row from the Register sheet

    Set rng = Me.Range("A" & Target.Row & ":D" & Target.Row)

    rng.Copy ws.Range("A" & lastRow)


    

    ' Delete the original row

    rng.Delete Shift:=xlUp


    

    ' Reprotect both sheets

    Me.Protect Password:=pw

    ws.Protect Password:=pw

End If
SafeExit:

   Application.EnableEvents = True

End Sub

Note:

  • Replace "yourpassword" with your actual sheet password, or leave it blank ("") if no password.
  • The macro disables events (Application.EnableEvents = False) to prevent recursive triggering.
  • Both the source sheet and destination sheet are unprotected before modification and then protected again.
  • The SafeExit label ensures that events are re-enabled even if an error occurs.

Was this answer helpful?

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. IGYQ 22,340 Reputation points Independent Advisor
    2025-10-27T17:44:54.59+00:00

    Let me know once done so I assist you further.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.