Thread: Code Only Running Manually

Hello -

I'm including the following code within another set of Excel VBA that has worked fine. The purpose of the following code is to remove a formula by pasting special values.

When I highlight and run all of the code manually, this newly added code component works. When I run the code by clicking the button it's associated with, the original code continues to work fine, but this newly added code pieced does not.

I don't think it's an Excel worksheet protection issue, as the same issue occurs when I remove all protection.

Help would be greatly appreciated.

With Sheet16
    ActiveSheet.Unprotect Password:="password"
    Range("B21:C21").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
    ActiveSheet.Protect Password:="password"
    Application.CutCopyMode = False    
End With

Thanks....

Hi PasteSpecialValues

Only guessing without seeing the entire code. You can try modifying the code like this

With Sheet16
    ActiveSheet.Unprotect Password:="password"
    .Range("B21:C21").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
    ActiveSheet.Protect Password:="password"
    Application.CutCopyMode = False    
End With

If you look carefully, you'll see I changed this line of code by adding a dot (.) before it. This ties the range to the "with" worksheet (Sheet16) and not the active worksheet.

.Range("B21:C21").Select

Hello Aaron,

Try this method.

With Sheet16
    .Unprotect Password:="password"
    .Range("B21:C21").Value = .Range("B21:C21").Value
    .Protect Password:="password"
End With