0

I have some code in VBA, I have several Country and Product buttons.

My goal is when I click a button, Cells (1, 1) will be overwrite with a text like "US", "CA", "MX" and cells (2, 1) with "PC", "Printers"

Example: If Cells (1, 1) = US Then select a Range, copy the Range and finally paste it in the same sheet but as a picture and in a different Range.

But firts I need to delete the previous picture to replace it with the new one.

It works, but sometimes it appears a message with the following error and doesn't paste the range.

'runtime error 1004 - microsoft excel cannot paste the data'

The code to delete pictures.

Sub Delete_Pictures()
    For Each Shape In ActiveSheet.Shapes
        If Left(Shape.Name, 7) = "Picture" Then
           Shape.Delete
        End If
    Next
End Sub

Let's suppose I already click on PC Button and Cells (2, 1) contains = 'PC'

If I click US Button the macro should do the following.

Sub Button_US()
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Cells(1, 1) = "US"

    Call Delete_Pictures

       If Cells(2, 1) = "PC" Then
       Range("BP73:BX87").Select
       Selection.Copy
           With Worksheets("Main")
               .Activate
               .Range("Z7").Select
               .Pictures.Paste(Link:=True).Select
           End With
       End If

   Application.CutCopyMode = False

   Application.ScreenUpdating = True
   Application.EnableEvents = True

 End Sub

I want to know if there is a way to correct this one, or if there are other ways to copy and paste a range without this kind of errors.

Luis Lara
  • 75
  • 2
  • 9
  • what is that `Call Delete_Pictures` for? Isn't it removing all pictures from the same worksheet whose `Range("BP73:BX87")` you're hoping to copy pictures from? – user3598756 May 25 '16 at 18:06
  • Possible duplicate of [VBA Runtime Error 1004 "Application-defined or Object-defined error" when Selecting Range](http://stackoverflow.com/questions/17980854/vba-runtime-error-1004-application-defined-or-object-defined-error-when-select) – Ken White May 25 '16 at 18:08

2 Answers2

5

I'm betting it's the .Select. See this thread on how to avoid using .Select, which can cause issues. 

Sub Button_US()
Dim myPic As Picture
Application.ScreenUpdating = False
Application.EnableEvents = False

Cells(1, 1) = "US"

'Call Delete_Pictures

If Cells(2, 1) = "PC" Then
    Range("BP73:BX87").Copy
    With Worksheets("Main")
        Set myPic = .Pictures.Paste(Link:=True)
        myPic.Left = .Range("Z7").Left
        myPic.Top = .Range("Z7").Top
'        .Range("Z7").Pictures.Paste(Link:=True).Select
    End With
End If

Application.CutCopyMode = False

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

(Also, I got some insight from this thread)

Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • 1
    You were right. Using .Select was giving me issues. I already have a couple hours using your code and haven't appeared errors. – Luis Lara May 25 '16 at 22:28
0

I had a similar issue when i try to paste some range as a picture. Error message was inconsistent and appeared time to time. I have managed to overcome this issue simply by adding some wait time between copy and paste lines.

Try adding 1 second wait time between copy and paste:

Sub Button_US()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Cells(1, 1) = "US"

    Call Delete_Pictures

       If Cells(2, 1) = "PC" Then
       Range("BP73:BX87").Select
       Selection.Copy
       Application.Wait (Now + TimeValue("00:00:01"))''1 second delay
           With Worksheets("Main")
               .Activate
               .Range("Z7").Select
               .Pictures.Paste(Link:=True).Select
           End With
       End If

    Application.CutCopyMode = False

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

It is not the most efficient way but it worked for me.

Gurerg
  • 1