Sheet protected error

[sound]

Hello folks,

I´m not so talented in writing macros and I´ve spending the last few days trying to understand what´s happening...

I have 3 sheets in a workbook. Sheet1 and Sheet2 are protected Sheet3 don´t.

Sheet1 contains the button that triggers the macro and the data to be stored in Sheet2.

When all the sheets are unprotected the macro works fine but when I protected sheet1 it fails. Even with sheet2 unprotected!!

Error: 1004. The cell you´re trying to change to change is on a protected sheet. And it highlights .End(xlDown).Offset(1, 0).Value = Worksheets("Sheet1").Range("E38").

Note: OP is a excel Table.

Can someone help me?

Sub Nota()
      
    With Worksheets("Sheet2").Range("OP")
        
        .End(xlDown).Offset(1, 0).Value = Worksheets("Sheet1").Range("E38")
        .End(xlDown).Offset(0, 1).Value = Worksheets("Sheet1").Range("G38")
        .End(xlDown).Offset(0, 2).Value = UCase(Worksheets("Sheet1").Range("C41"))
        .End(xlDown).Offset(0, 4).Value = UCase(Worksheets("Sheet1").Range("E41"))
        .End(xlDown).Offset(0, 5).Value = Worksheets("Sheet1").Range("G41")
    
    End With
    Range("E38,G38,C41,E41,G41").ClearContents
    
    
End Sub

 

[Trebor76]

Hi sound,

Welcome to MrExcel!!

Try this - just substitute the text YourPasswordHere with whatever you've to protect Sheet1 and Sheet2:

Option Explicit
Sub Nota()
      
    With Worksheets("Sheet2")
    
        .Unprotect Password:="YourPasswordHere"
        
        With .Range("OP")
        
            .End(xlDown).Offset(1, 0).Value = Worksheets("Sheet1").Range("E38")
            .End(xlDown).Offset(0, 1).Value = Worksheets("Sheet1").Range("G38")
            .End(xlDown).Offset(0, 2).Value = UCase(Worksheets("Sheet1").Range("C41"))
            .End(xlDown).Offset(0, 4).Value = UCase(Worksheets("Sheet1").Range("E41"))
            .End(xlDown).Offset(0, 5).Value = Worksheets("Sheet1").Range("G41")
            
        End With
        
        .Protect Password:="YourPasswordHere"
    
    End With
    
    With Worksheets("Sheet1")
    
        .Unprotect Password:="YourPasswordHere"
    
        .Range("E38,G38,C41,E41,G41").ClearContents
        
        .Protect Password:="YourPasswordHere"
        
    End With
    
End Sub

Regards,

Robert