Weird Excel Behaviour

ercedwards

Board Regular
Joined
Apr 27, 2013
Messages
125
I have the following code

Code:
Sub AddPicture(ByRef WinType As String, MRow As Integer)
On Error GoTo Start
Start: Sheets("Window Pics").Select
       ActiveSheet.Shapes.Range(Array(WinType)).Select
       Selection.Copy
       Sheets("Mulled Windows").Select
       Range("C" & MRow).Select
       ActiveSheet.Paste
       Application.CutCopyMode = False
End Sub

This code runs perfectly fine on my laptop. When I move it to another machine it runs fine for a bit and then all of a sudden I get
Paste Method of Worksheet Class Failed at the ActiveSheet.Paste line.

So, in Debug mode if I move the arrow up to Start and press F5 it continues to run fine and then bingo, I randomly get the same error. That's why I added the On Error line but Excel seems to ignore that.
Thoughts? It's driving me nuts.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
You have another macro running in your file.
That error occurs because the memory is clean and cannot paste something.
Then maybe something is clearing the memory.
 
Upvote 0
Dante,
All the best to you for 2020.
I might agree with you if this happened on every computer and every time.
This macro is called in a loop in another macro and simply moves between 1 and 20 pictures from one worksheet to another.
If I only have to move 1 or 2 pictures I never get an error. Sometimes it will move 3, 4 or 5 pictures with no error and then suddenly the error pops up again.
If something was clearing the memory why wouldn't it happen every time?
 
Upvote 0
Does this work any better?
VBA Code:
Sub AddPicture(ByRef WinType As String, MRow As Integer)

    Sheets("Window Pics").Shapes.Range(Array(WinType)).Copy

    With Sheets("Mulled Windows")
        Application.Goto .Range("C" & MRow).Select
        .Paste
    End With

    Application.CutCopyMode = False

End Sub
 
Upvote 0
Perhaps the memory is being cleaned between each copy because the process is very fast.
Try the following:

VBA Code:
Sub AddPicture(ByRef WinType As String, MRow As Integer)
  Application.ScreenUpdating = False
  Sheets("Window Pics").Select
  ActiveSheet.Shapes.Range(Array(WinType)).Select
  Selection.Copy
  Sheets("Mulled Windows").Range("C" & MRow).PasteSpecial xlPasteAll
  DoEvents
End Sub

Or this

VBA Code:
Sub AddPicture(ByRef WinType As String, MRow As Integer)
  Application.ScreenUpdating = False
  Sheets("Window Pics").Select
  ActiveSheet.Shapes.Range(Array(WinType)).Select
  Selection.Copy
  Sheets("Mulled Windows").Range("C" & MRow).PasteSpecial xlPasteAll
  DoEvents
  Application.Wait (Now + TimeValue("00:00:01"))
  DoEvents
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top