Run time error 1004 - Paste special method of range class failed

[Diogenes]

Hi all,

I have quite a bizarre error occurring with the code below - when the button is pressed to run this code I get the Run time error 1004 - Paste special method of range class failed. However if I step through the code no error occurs.

The error is occurring at the "paste special" stage of the code.

Could anyone please help explain why it seems to appear when running but not when stepped through and help me to get rid of the error when the button is pressed?

Thanks in advance,

Sub Remove_duplicates()'
' Remove_duplicates Macro
    Sheets("Working").Visible = True
    Application.GoTo Reference:="All_tbs"
    Selection.Copy
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Range("$F$4:$G$110145").Removeduplicates Columns:=Array(1, 2), _
        Header:=xlNo
  
  Application.GoTo Reference:="new_tb_codes"
    Selection.Copy
    Sheets("Input").Select
    ActiveSheet.Unprotect
    Range("B26").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Protect
   Sheets("Working").Visible = False
        
End Sub

 

[njimack]

You don't need to select sheets (or even make them visible) to manipulate them...

Sub Remove_duplicates() 

'' Remove_duplicates Macro

Application.ScreenUpdating = False


Range("All_tbs").Copy


With Sheets("Working")
    .Range("F4").PasteSpecial xlValues
    .Range("$F$4:$G$110145").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
End With


Range("new_tb_codes").Copy
    
With Sheets("Input")
    .Unprotect
    .Range("B26").PasteSpecial xlValues
    .Protect
End With


Application.ScreenUpdating = True
        
End Sub

 

[Diogenes]

Thanks - certainly a lot cleaner now although I still get the same error...

Could it be something to do with data on the clipboard? Or should that have no bearing on it?

 

[Momentman]

On which line do you get the error?

Could this be the problem

.Range("B26").PasteSpecial xlValues

change to

Range("B26").PasteSpecial xlPasteValues

 

[Diogenes]

Thanks - that is the line that the error is occurring on.

I have run the code with that change and the error is still occurring - however once again if I step through the code line by line it seems to be fine?!

 

[MARK858]

Does the amendment below make any difference?

With Sheets("Input")
    .Unprotect
    [COLOR="#FF0000"]Application.Wait Now + TimeValue("00:00:05")[/COLOR]
    .Range("B26").PasteSpecial xlPasteValues
    .Protect
End With

 

[Diogenes]

The same error still occurs.

Although I notice that with or without that amendment if I run the code twice i.e run it then quickly run it again the error will occur on the first time but not the second and on it goes alternating from working to not working.

Its all very confusing!

 

[MARK858]

What about with?...

With Sheets("Input")

    .Unprotect
     Range("new_tb_codes").Copy
    .Range("B26").PasteSpecial Paste:=xlPasteValues
    .Protect
End With

 

[Diogenes]

Ran that 6 times in a row with no trouble - fingers crossed it just keeps on doing that!

It shouldn't matter that the range "new_tb_codes" refers to another sheet but is grouped in the With Sheets ("Input") code?

Thank you for all your help

 

[MARK858]

It shouldn't matter that the range "new_tb_codes" refers to another sheet but is grouped in the With Sheets ("Input") code?

Not at long as you don't put a fullstop/period in front of the word Range which would make it part of the With statement