Copy and Paste shapes from one sheet to another sheet.

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
In the below part of the code, I have a UserForm ComboBox in which I highlight (select) a name.
Next I will click on one of 5 buttons that have colors as captions.
The relevant color shapes are very small, the height of a regular cell height and somewhere around one quarter of the width of a regular cell, and are in another sheet.
For now, the sheet name where these colored shapes are located is a sheet named "Pictures" (no double quotes)
The color shape needs to be copied from the sheet where they are and pasted into the cell with the name that I selected in the ComboBox.
It alls works good with the odd exception when it comes up with an error stating "Paste method of worksheet class failed."
For days on end, it works without problems and then out of the blue it will error.
I have tried with copy and paste, copy and pastespecial, with and without Do Events and whatever I found by googling.
The current way, "Duplicate.Cut", is the least problematic so far but still errors every so once in a while.
I have not been able to find a surefire way that will not error.
If I save the shapes in a folder and use "Pictures.Insert" I don't get the error message but for convenience sake I would prefer to copy/paste between sheets.
Is there a guaranteed way that will work?
Thanks in advance.

Code:
With ThisWorkbook.Sheets("Pictures")
    .Shapes(a).Duplicate.Cut    '<---- a = UserForm1.ActiveControl.Caption
        DoEvents
    .Paste Destination:=ActiveSheet.Columns(3).Find(UserForm1.ComboBox1, , , 1) 
End With
With Selection
    .Name = a    '<---- a = UserForm1.ActiveControl.Caption
        .Top = Columns(3).Find(UserForm1.ComboBox1, , , 1).Top
    .Left = Columns(3).Find(UserForm1.ComboBox1, , , 1).Left + Columns(3).Width - Selection.Width * j    '<----- j is just a width multiplier
End With
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try this
Rich (BB code):
Dim cel As Range
Set cel = ActiveSheet.Columns(3).Find(UserForm1.ComboBox1, , , 1)

With ThisWorkbook.Sheets("Pictures")
    .Shapes(a).Duplicate.Cut
    cel.Parent.Activate
    cel.Activate
    ActiveSheet.Paste
End With

With Selection
    .Name = a    '<---- a = UserForm1.ActiveControl.Caption
        .Top = Columns(3).Find(UserForm1.ComboBox1, , , 1).Top
    .Left = Columns(3).Find(UserForm1.ComboBox1, , , 1).Left + Columns(3).Width - Selection.Width * j    '<----- j is just a width multiplier
End With
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Thank you very much.
I'll implement this first thing in the morning.
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Out of 2 tries with 90 copy/pastes, errored 2 times each try at "ActiveSheet.Paste.
Can't say if it is better or not as the previous code also errored sporadically only.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

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
Top