#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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)
|
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
ok, all shapes, I will delete the ones that I don't need, no problem there.
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
hello Venky, is there a chance that I can send you the program I am working on for you to see?
|
#9
|
|||
|
|||
Yes, please do.
|
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 & 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 |