Error Message = Microsoft Excel cannot paste the data (Run-time error '1004')

[WallisAggie646777]

The following 6 lines of code are contained in a worksheet Command button in the "Weekly" worksheet
When I click on the command button, the cannot paste error occurs.

With Sheets("Groups")
.Range(wsGroups.Cells(MonR, GroupC), wsGroups.Cells(GroupFriR + 4, GroupEndC)).Copy '
End With
wsWeekly.Activate ' "Weekly" worksheet
Range("A40").Select
ActiveSheet.Pictures.Paste(Link:=True).Select :The cannot paste error occurs on this line of code.

I can set a breakpoint on the line of code causing the error and then click on the Command button.
Execution stops on the line where the error occurred. I then use F8 to Step into to execute the line of code and it works by pasting the data as expected

In summary, with no breakpoint, the code fails when clicking on the Command button, but with the breakpoint, I can click on the Command button and then use F8 to step into the line of code and the code pastes the data as expected.

 

[Kenneth Hobson]

I seldom use activate, select, selection, or such. In this case, it is appropriate. As such, both the commented select method and the other works for me.

Sub Test()
  Sheet1.[A1:B2].Copy
  Sheet2.Activate
  [A1].Select
  'ActiveSheet.Pictures.Paste(Link:=True).Select
  ActiveSheet.Pictures.Paste True
  Application.CutCopyMode = False
End Sub

 

[WallisAggie646777]

I also replaced the breakpoint on the ActiveSheet.Pictures.Paste(Link:=True).Select line of code with a Msgbox just before the ActiveSheet.Pictures.Paste(Link:=True).Select line of code and the paste works as expected when I replied to the Msgbox

 

[WallisAggie646777]

I tried the code Kenneth provided and I get the same error, but when I insert the Msgbox line above the .Paste code, it works

 

[Kenneth Hobson]

See if you can duplicate the problem with a simple example in a new blank workbook. I have no way to test your ranges.

Sometimes, merged cells or protected cells will cause issues. Since your break method works, I don't see that those are a factor here.

 

[WallisAggie646777]

I created a new Spreadsheet as Kenneth suggested with the code shown below, some text data in the sheet "Groups" from "A1:B2" and the Command Button is all that is in the spreadsheet.
In the sheet "Groups" I e3ntered the following data:
cell A1 = "Text in A1"
cell A2 = "Text in A2"
cell B1 = "Text in B1"
cell B2 = "Text in B2"
In my original worksheet delete the picture pasted into the "Weekly" sheet with code before the new paste occurs, but in this example, I manually delete the pasted picture before running the code

Private Sub CommandButton1_Click()
Dim wsWeekly As Worksheet
Set wsWeekly = Sheets("Weekly")
Sheets("Groups").Range("A1:B2").Copy
wsWeekly.Activate ' "Weekly" worksheet
[A40].Select
MsgBox ("Press OK to Continue") ' WORKS WITH MSGBOX CODE AND FAILS WITH CANNOT PASTE THE DATA IF THE MSGBOX LINE IS COMMENTED OUT
ActiveSheet.Pictures.Paste True
Application.CutCopyMode = False
End Sub

 

[Kenneth Hobson]

I guess this is a case where not posting your full code, I did not see the issue.

Your issue is a conflict as your activeworksheet is the worksheet where your ActiveX control's Private Sub resides. If you are going to do it that way, then set the worksheet object with your A40 range. This sort of issue crops up every so often with worksheet object code that works fine as a Module but not in the worksheet object. You have to be more literal in other words.
e.g.

Private Sub CommandButton1_Click()
  Dim wsWeekly As Worksheet
  Set wsWeekly = Worksheets("Weekly")
  Worksheets("Groups").Range("A1:B2").Copy
  With wsWeekly
    .Activate ' "Weekly" worksheet
    .Range("A40").Select
    'MsgBox ("Press OK to Continue") ' WORKS WITH MSGBOX CODE AND FAILS WITH CANNOT PASTE THE DATA IF THE MSGBOX LINE IS COMMENTED OUT
    .Pictures.Paste True
  End With
  Application.CutCopyMode = False
End Sub

 

[WallisAggie646777]

I still get the cannot paste error message using the code provided.

What I am trying to do is the following:
1. Click on either an Active X control or a Form Control to:
1. Copy a range of cells copy a range of cells in worksheet 1.
2. Copy the selected cells into worksheet 2 starting in a different cell and retain the data
and column widths from worksheet 1 as a picture or other object because the columns widths in worksheet 2 are not the same worksheet 1.
3. Next time I run the code, I need to be able to identify and delete the copied shape in worksheet 2 before I do the copy and paste the next time the code is run

Any help would be much appreciated!

 

[Kenneth Hobson]

I can not help further unless I see your problem file or at least a simple version that has the problem. This forum does not allow attachments unfortunately.

Here is a link to an example file. https://www.dropbox.com/s/pnk458jdjl8ofu7/Test CopyPaste Range from Private Sub.xlsm?dl=1

For item (2) in #8, have you considered a Copy and PasteSpecial? You will have the same problem though I suspect unless you take care as I showed.

 

[WallisAggie646777]

Kenneth, I really really appreciate your help, but still no luck.

I ran the spreadsheet you provided to me and it gets the error: Run Time error '1004': Microsoft Excel cannot paste the Data

I am trying to Paste from the "Groups" worksheet" to the "Weekly" worksheet.
The column widths in "Groups" and "Weekly" are different.
I need the copy to the Weekly range of "O7:T32" to the "Groups" worksheet at "A40" and retain the values, column widths and borders that exist in the "Groups" worksheet without changing any column widths in the "Weekly" worksheet.

The following three lines of code are run from an Active X control in a separate worksheet named "Test"

Sheets("Groups").Range("O7:T32").Copy
Sheets("Weekly").Range("A40").Select
Sheets("Weekly").Pictures.Paste True ' I get the same 1004 error I get with your spreadsheet

I get the error Run Time error '1004': Microsoft Excel cannot paste the Data

I assume this is tied to what you were explaining to me earilier about the issue of where the Active X control reside, but I really don't understand enough to know what to do.
I also tried running the code from a public sub in a module and called from an ActiveX control in the Test Worksheet and I get the same error.


I am a retired computer guy who is 80 years old and don't program as well as I use to do.
If you have an easy way to tell me how to change the code or put it somewhere else, I would appreciate knowing.

If you are tired of looking at this issue and want to quit working on it, I fully understand and appreciate what you have done.
Thanks,
Wallis