Selecting sheet using a variable in Macro

**adamNBU** · 04-06-2010, 08:31 AM

Dom, thanks for the code!

It works when the selected worksheet is not password protected (and it does protect it at the end). But if the selected worksheet is protected, it fails with "run-time error 1004" "PasteSpecial Method of Range Class Failed".

**adamNBU** · 04-06-2010, 08:59 AM

Dom, thanks for the code!

It works when the target worksheet is NOT password protected. The macro runs and then protects the ws at the end. However, if the target WS is protected, the macro fails with the message: "Run time error 1004: PasteSpecial method of Range class failed". The code is exactly as you have posted.

--adam

**Shampoo Monkey** · 04-06-2010, 09:30 AM

Hey Adam,

Here is an adjustment to Dom's code. It checks to see if the worksheet is orgininally protected, then acts accordingly. Also untested:

Dim myWs As Worksheet
Dim pass as Boolean

Set myWs = Sheets("" & Range("M8") & "")

Range("L8:Z41").Copy

With myWs
    
    pass = .ProtectContents

    If pass = True Then .Unprotect Password:="Secret"

    .Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
    If pass = True then .Protect Password:="Secret"
    
End With
        
ActiveWorkbook.Save

**adamNBU** · 04-06-2010, 09:54 AM

ShampooMonkey,

I tried your code but the same problem persists.

error: Run time error 1004: PasteSpecial method of Range class failed"
and highlights ".Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False"

my code:

Sheet1.Unprotect Password:="Secret"
    Application.ScreenUpdating = False
    Answer = MsgBox("This will be permanent and will re-write any data you have saved for this month, independent of the year.", _
    vbOKCancel, "These changes will be perminent.")
    If Answer = vbCancel Then Exit Sub
    Dim MonthWS As Worksheet
    Dim pass As Boolean
    Set MonthWS = Sheets("" & Range("M8") & "")
    Range("L8:Z41").Copy
    With MonthWS
    pass = .ProtectContents
    If pass = True Then .Unprotect Password:="Secret"
    
    .Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        
    If pass = True Then .Protect Password:="Secret"
    
    End With
    Application.ScreenUpdating = True
    ActiveWorkbook.Save
    CreateObject("WScript.Shell").Popup "Saved.", _
    1, "This message will close in 1 sec."
    Sheet1.Protect Password:=""
    End Sub

**Shampoo Monkey** · 04-06-2010, 10:08 AM

Hey Adam,

If the worksheet is protected you will get that error, the line

If pass = True Then .Protect Password:="Secret"

is used to unprotect your worksheet. If the worksheets has a password that is not Secret, you need to replace "Secret" with your password.

Let us know if this solves the issue or if its still not working for you.

**buran** · 04-06-2010, 10:13 AM

Hi Adam,
Are you sure it highlights all of this code:

.Range("C4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

Because these are two different lines of code and if it PasteSpecial method fails it should not highlight

Application.CutCopyMode = False

Make sure these are separate lines.

Buran

**buran** · 04-06-2010, 10:19 AM

@Shampoo Monkey
If the password is not correct it will return different error message: 1004, Password you supplied is not corect. Verify that CAPS LOCK key is off and be sure to use correct capitalization.

**adamNBU** · 04-06-2010, 12:08 PM

@Buran, you are correct, the error debug only highlights the first part as you have guessed.

@GreeceMonkey, I checked the pw and it is correct. I even tried protection without a pw ("") but it still results in the same problem.