ActiveSheet.Paste does not work on other PC

Question

I copy some cells from one worksheet manually using the ctrl + c command and want to paste it using a macro I created.

I have the following code:

Range("A2:W5000").Select
Selection.ClearContents
Range("A2").Select
ActiveSheet.Paste
With Selection.Interior
    .PatternColorIndex = 7
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With
Range("A2").Select

This macro runs just fine on my machine, but for some reason, I get an error with the ActiveSheet.Paste when running the exact same macro on another PC.

Any ideas on why this could be happening?

Thank you in advance for any suggestions.

First of all you should avoid using select: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And specify a sheet name for every range like `Worksheets("SheetName").Range(…)` and chances are good that the issues are gone already. Also I see no `Copy` action so this might cause an error too. — Pᴇʜ, Mar 20 '18 at 16:07
What on earth are you trying to accomplish. You don't copy anything but clear a bunch of cells that would potentially cancel any existing copy operation. Then you select the top-left cell from the original selection and attempt a paste. The rest won't ever be reached due to the operation being halted there. — , Mar 20 '18 at 16:22
Hi, thank you for your replies. I use the ctrl + c command on another worksheet and want to past it using the macro, which works fine on my PC but not on another. Sorry, forgot to mention that in the question. — Serge Inácio, Mar 20 '18 at 17:01

score 1 · Answer 1 · answered Mar 20 '18 at 20:06

1

I think PEH has the right answer. But I updated your code to reflect

Dim ws as Worksheet

set ws = ActiveSheet 'Setting the worksheet object and then referencing it for each Range will ensure that the macro doesn't get confused as to which sheet it should be getting the Range from.
ws.Range("A2:W5000").ClearContents 'No need to select cells first before clearing them
ws.Range("A2").PasteSpecial 'Once again, no need to select before pasting. It will do a normal paste if you do PasteSpecial only, but if you wanted to say paste values only it would look like this .PasteSpecial(xlPasteValues) 

With ws.Range("A2").Interior
    .PatternColorIndex = 7
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With
ws.Range("A2").Select 'No necessary unless you think that A2 won't be visible when the other user uses this macro. No harm in leaving it in though.

Hopefully, this helps you. Good luck! Jason

answered Mar 20 '18 at 20:06

Jason Brady

1,560
1
17
40

The actual issue is that `.ClearContents` kills the `CutCopyMode` so nothing is selected for copy anymore, as I pointed out in [my answer](https://stackoverflow.com/a/49400351/3219613). – Pᴇʜ Mar 21 '18 at 07:18
Everything works on my laptop. It is correct that `.ClearContents` kills the `CutCopyMode`, but I think that is only if the copied content is in the same workbook. I have tried this solution but I get the same runtime error on the other pc. – Serge Inácio Mar 21 '18 at 12:00
1

@SergeInácio Of course you get the same runtime error. `.ClearContents` is known to kill the `CutCopyMode` even if the copied content is NOT in the same workbook. That just won't work if you copy a whole cell. This only works if you copy the content of inside a cell, because then the Windows clipboard is used instead of the MSO clipboard. `.ClearContents` Will only kill the MSO clipboard but not the Windows clipboard. – Pᴇʜ Mar 21 '18 at 14:14
Thanks @Pᴇʜ , I understand that, and would understand it even better if it did not work on my laptop. The problem is that this macro just does not work on another laptop, which I find strange. On my laptop if I copy cells from another workbook and run the macro, all the content is pasted as expected, and when I go back to the other workbook the cells are still copied. – Serge Inácio Mar 21 '18 at 15:27
1

@SergeInácio I could imagine (didn't test it), that probably your other workbook is not in the same Excel instance? Nevertheless the odd thing is that it works on your laptop (it shouldn't at all). You cannot trust it. So that the cutcopymode gets reseted by `.ClearContents` is the normal behavior. You should use copy after `.ClearContents` or omit `.ClearContents`. – Pᴇʜ Mar 21 '18 at 15:40
Thanks @Pᴇʜ , I did a different macro to clear the contents, so I can copy paste aftarwards, and that works. I do not have a valid answer yet on the list so I cannot accept. If you add an answer I will gladly accept it. Thanks again – Serge Inácio Mar 22 '18 at 14:04
@SergeInácio I already wrote one [here](https://stackoverflow.com/a/49400351/3219613). – Pᴇʜ Mar 22 '18 at 14:16

Pᴇʜ · Accepted Answer · 2018-03-22T14:15:14.853

The issue is that you start copy before you run that macro. But if you use .ClearContents in your macro the copy selection gets lost.

Therefore .PasteSpecial after .ClearContents cannot work.

you can easily test this with

Sub test()
    Range("A1").Copy
    Debug.Print Application.CutCopyMode '=1 means something is copied
    Range("A2").ClearContents           'kills cutcopymode
    Debug.Print Application.CutCopyMode '=0 means nothing is copied
    Range("A3").PasteSpecial            'fails because nothing is selected for copy anymore
End Sub

So the solution would be …

not to use .ClearContents or any other action that kills the copy selection before .Paste.
write a procedure that …
1. .ClearContents first and then
2. Copies the desired range (eg. Selection) and finally
3. Pastes

score 0 · Answer 3 · answered Mar 20 '18 at 16:11

Most probably you are working protected worksheets. Thus, you are getting 1004 error. Try to check whether teh worksheet is protected before doing anything with it:

Sub TestMe()
    If ActiveSheet.ProtectContents Then
        MsgBox ActiveSheet.Name & " is protected!"
    Else
        Range("A2:W5000").Select
        Selection.ClearContents
        Range("A2").Select
        ActiveSheet.Paste
        With Selection.Interior
            .PatternColorIndex = 7
            .ThemeColor = xlThemeColorAccent2
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
        Range("A2").Select
    End If
End Sub

ActiveSheet.Paste does not work on other PC

3 Answers3