Solved

copy paste embedded shapes between hidden worksheets

Posted on 2013-11-16
8
333 Views
Last Modified: 2013-11-20
Hello,

The code below has been implemented to:

1. delete existing OLE shapes from hidden worksheet 1
2. copy new OLE shapes from hidden worksheet 2
3. paste the copied shapes in hidden worksheet 1

The command button is on a worksheet 3, which is the only visible sheet to the user.

The code works fine for steps 1 and 2, but doesn't work for step 3 (pasting the shapes to hidden worksheet 1).

The code is as follows:

Private Sub CommandButton1_Click()


 ThisWorkbook.Worksheets("6").Shapes.Range(Array("1", "2", "3", "4", "5")).Delete

 
    ThisWorkbook.Worksheets("7").Shapes.Range(Array("1", "2", "3", "4", "5")).Select
    Selection.Copy
   
       ThisWorkbook.Worksheets("6").Select
    Selection.Paste

End Sub


The answer appears so close - all help appreciated.
0
Comment
Question by:Senechal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39653576
replace this
Selection.Paste

by this
Selection.PasteSpecial

gowflow
0
 

Author Comment

by:Senechal
ID: 39654183
Hello gowflow,

Thank you for the suggestion, but the results are still the same as before: the deletion takes place OK, but not the copy and paste. I'm attaching a demo file in case this is any help.
DemoCopyPasteOLE.xlsm
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 39654221
Here is it
gowflow
DemoCopyPasteOLE.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Senechal
ID: 39654737
Hi goflow,

Your solution works perfectly. Thank you so much.
0
 

Author Comment

by:Senechal
ID: 39654745
I've requested that this question be closed as follows:

Accepted answer: 0 points for Senechal's comment #a39654737

for the following reason:

Solution by goflow.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39654746
I am objecting to this closure as supposed the asker made a mistake in closing it by awarding himself 0 points but no points to the correct answer. His comments clearly indicate that I provided the solution for him.
Tks/gowflow
0
 

Author Comment

by:Senechal
ID: 39655676
Gowflow provided an excellent solution and I cannot thank him enough for his courtesy and speed. It is absolutely worth 500 points. This is the first time I've used this system, so the mistake in point-setting was entirely mine.
0
 
LVL 30

Expert Comment

by:gowflow
ID: 39656725
No problem don't worry I thought so. Pls feel free to put any comment on this question case you need further help with any other question. I will keep this one monitored.
gowflow
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question