VBA Excel issue when pasting charts

Question

It's a first time I'm writing for help so sorry if any conventional errors, but I'm looking for over a month for a solution to my problem and couldn't find it anywhere.

I've got a code that is stored on the shared drive for my team. They use it by clicking on different buttons on ribbon to run VBA scripts, so all guys are using exactly the same macro. Also most of their local environmental variabilities are the same (some of them has added local keyboards, but uninstalling them was not solving the issue). The same sub is run every time in the script 13 times (13 iterations for 13 charts to copy from different sheets to gather them in one).

This is a sub:

Sub Movechart(TabName, ChartName, PasteRange)


Sheets(TabName).Activate

'this is to walk-over part of errors that were caused by not executing line above
If ActiveSheet.Name <> TabName Then
    Sheets(TabName).Select
End If

Range("a1").Select
ActiveSheet.Shapes(ChartName).Copy
        
Sheets("MAIL").Select
Range(PasteRange).Select
ActiveSheet.Paste
ActiveSheet.Shapes(ChartName).IncrementTop 1
ActiveSheet.Shapes(ChartName).IncrementLeft 1

Application.CutCopyMode = False
End Sub

On few computers it was causing sometimes error while executing this sub. Some guys are facing a problem only once of 13 iterations, other few times, some of them never. Also I am myself facing the same error but not every time when running the script. I have noticed that it usually doesn't give error, when I'm testing some changes and I have anywhere before the breaking point.

The script itself is pretty massive, there is few modules but the process to automate was taking an 1h manually.

The execution of code stops in line

ActiveSheet.Paste

Some of stops were caused by not executing line

Sheets(TabName).Activate

So I made a walk-around visible in the code. Some of them are still occurring, resulting in this error:

Run-time error '1004': Paste method of Worksheet class failed

When I stop macro in that place and I will manually try to paste (CTRL+V), it gives me a chart from the previous iteration, so it looks like it "skips" executing line

ActiveSheet.Shapes(ChartName).Copy

and then it got some internal mishmash error, rather than what's written in actual error prompt.

I want to add than machines are pretty powerful, we got 16GB RAM on each VM. The error has started to occur when we have moved from Win7 -> Win10, the script was in use for a long time before without problems.

I have already tried to use only 1 of 4 processors as well as disabled clipboard history that helped one guy only:)

Your help would be appreciated!

Answer 1

Thank you a lot for your answer. It seems that combining a loop repeating copy-pasting and

DoEvents

helped. This is the working code now:

Sub Movechart(TabName, ChartName, PasteRange)
Dim Check As Integer

Check = 0
Do While Check < 20
    Sheets(TabName).Activate
    
    If ActiveSheet.Name <> TabName Then
        Sheets(TabName).Select
    End If
    
    Range("a1").Select
    ActiveSheet.Shapes(ChartName).Copy
            
    Sheets("MAIL").Select
    Range(PasteRange).Select
    On Error Resume Next
        ActiveSheet.Paste
        If Err.Number <> 0 Then
            DoEvents
            Check = Check + 1
            If Check > 19 Then
                MsgBox "Error with pasting charts, pls contact developer"
            End If
        Else
            Check = 20
            Exit Do
        End If
    On Error GoTo 0
Loop

ActiveSheet.Shapes(ChartName).IncrementTop 1
ActiveSheet.Shapes(ChartName).IncrementLeft 1

Application.CutCopyMode = False
End Sub