Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-12-2012, 05:22 AM
Marcus Marcus is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 64bit
Novice
copy a range including shapes
 
Join Date: Aug 2012
Posts: 4
Marcus is on a distinguished road
Default copy a range including shapes


hello everybody, I have a small problem: I need to create a macro to copy a range from one sheet (sheet1!a13:FU45). and paste it to another sheet of the same workbook (same range). The problem is that the range I would like to copy contains shapes and I would like the shapes to be copied to. The amount of shapes varies so I never know how many and where they are in that range. Anybody can help me out with this?

Thank you all for any help
Reply With Quote
  #2  
Old 08-12-2012, 11:41 PM
Venky62 Venky62 is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

The cells and shapes of Excel sheet belong to separate Object collections. We cannot refer to the shapes in a worksheet by using Cell ranges. We have to refer to them as part of Shapes Range or collection.

If all the shapes on your worksheet are to be included for copying on to another worksheet, then a macro can be easily written. Otherwise, we will have to refer them individually and copy them.

Also, note that when copying a shape from one sheet to another, the position of the shapes will change.
Reply With Quote
  #3  
Old 08-12-2012, 11:59 PM
Marcus Marcus is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 64bit
Novice
copy a range including shapes
 
Join Date: Aug 2012
Posts: 4
Marcus is on a distinguished road
Default

goodmorning Venky, can you please show me an example? All the shape in that range are to be included. I don't mind having to move them around after the paste process. The main thing is to have them on a different sheet as close as possible to the original position (if possible)
Reply With Quote
  #4  
Old 08-13-2012, 12:04 AM
Venky62 Venky62 is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

I will try to send a sample code soon, but to repeat once again, all shapes means all shapes on the sheet you are copying.
Reply With Quote
  #5  
Old 08-13-2012, 12:38 AM
Marcus Marcus is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 64bit
Novice
copy a range including shapes
 
Join Date: Aug 2012
Posts: 4
Marcus is on a distinguished road
Default

ok, all shapes, I will delete the ones that I don't need, no problem there.
Reply With Quote
  #6  
Old 08-13-2012, 06:58 AM
Venky62 Venky62 is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

Okay, here is the Excel file with code. If you click on the Macro symbol in the Quick access Toolbar, it will transfer the data from Sheet 1 to Sheet 2 - the data in the specified range and all shapes. I was able to find a way so that the shapes retain their original position.

I had a lot of problem trying to solve the copy and paste methods that this code involves. Looks like there is a bug in Excel when it comes to copying and pasting shapes. But eventually the code is running consistently. So let me know if you have any problems.

In your own worksheet, change the range as needed (instead of A1:C5, enter A13:FU45 (or whatever is the range). If the source worksheet is "Sheet1" and destination worksheet is "Sheet2", then no need to make any more changes, else, you need to make those changes too.

To see the code in this sample file, press Alt+F11 and double click on "This workbook" in the Project Explorer window.
Attached Files
File Type: zip Sample.zip (103.9 KB, 25 views)
Reply With Quote
  #7  
Old 08-15-2012, 03:07 AM
Marcus Marcus is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 64bit
Novice
copy a range including shapes
 
Join Date: Aug 2012
Posts: 4
Marcus is on a distinguished road
Default

Hello Venky, it isn't working. Later today I will explain what I get as a result when I run it.

Thank you very much so far
Reply With Quote
  #8  
Old 08-17-2012, 12:27 AM
s7y s7y is offline copy a range including shapes Windows 7 32bit copy a range including shapes Office 2010 32bit
Advanced Beginner
 
Join Date: May 2012
Posts: 38
s7y is on a distinguished road
Default

hello Venky, is there a chance that I can send you the program I am working on for you to see?
Reply With Quote
  #9  
Old 08-17-2012, 01:50 AM
Venky62 Venky62 is offline copy a range including shapes Windows 7 64bit copy a range including shapes Office 2010 32bit
Advanced Beginner
 
Join Date: Jul 2012
Posts: 58
Venky62 is on a distinguished road
Default

Yes, please do.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Send Email with VBA while also including formatting tylerg11 Outlook 0 07-27-2012 10:49 AM
copy a range including shapes Copy & Paste Shapes & Motion Paths from 1 Slide Size to Another buckaroobanzai PowerPoint 1 06-08-2012 05:32 AM
Loop through folder of workbooks and copy range to other workbook Snvlsfoal Excel Programming 3 07-29-2011 05:55 AM
counting occurences of a value in a range but not including 0's nor blanks virsojour Excel 0 07-05-2010 01:34 AM
Copy plain text message including sender, recipient, and subject distancesprinter Outlook 0 01-22-2010 12:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft