Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to PasteSpecial to a Different Worksheet?
I have a UDF that draws a guage on the worksheet it is embedded in using
common Office drawing shapes. I want to be able to copy a shape to the clipboard, and then paste it back in as a picture object. The reason I want to do this is that in Excel, text does not rotate when you rotate an object. If I convert the object to a metafile (picture), the text will rotate properly when I rotate the picture. I have an object named "Shp" which in this case is a rectangle with text in it. What I want to do is something like this: Shp.Copy ' <-- Copy the rectangle to the clipboard Set NewShp = Destination.Worksheet.PasteSpecial("Picture (Enhanced Metafile)", False, False) ' <-- Paste into a new object to manipulate "Destination" is the address of the cell containing the UDF, so "Destination.Worksheet" points to the worksheet on which the UDF is doing the drawing. It is not necessarily the active sheet, so I can't use "ActiveSheet.PasteSpecial" and I don't believe I can't refer to "Selection". I know the code above is wrong. Does anyone have a suggestion for how this might work? I'm working in 2003 SP3 these days. Thanks in Advance, Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to PasteSpecial to a Different Worksheet?
Eric
You mention a UDF? As far as I'm aware a UDF can't make changes to a worksheet, not even the one it's being used in. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to PasteSpecial to a Different Worksheet?
If Destination.Worksheet is the object your acting on, try replacing
ActiveSheet with Destination.Worksheet. I've not seen a worksheet object defined that way, so I'm not sure if it'll work. "EricG" wrote: I have a UDF that draws a guage on the worksheet it is embedded in using common Office drawing shapes. I want to be able to copy a shape to the clipboard, and then paste it back in as a picture object. The reason I want to do this is that in Excel, text does not rotate when you rotate an object. If I convert the object to a metafile (picture), the text will rotate properly when I rotate the picture. I have an object named "Shp" which in this case is a rectangle with text in it. What I want to do is something like this: Shp.Copy ' <-- Copy the rectangle to the clipboard Set NewShp = Destination.Worksheet.PasteSpecial("Picture (Enhanced Metafile)", False, False) ' <-- Paste into a new object to manipulate "Destination" is the address of the cell containing the UDF, so "Destination.Worksheet" points to the worksheet on which the UDF is doing the drawing. It is not necessarily the active sheet, so I can't use "ActiveSheet.PasteSpecial" and I don't believe I can't refer to "Selection". I know the code above is wrong. Does anyone have a suggestion for how this might work? I'm working in 2003 SP3 these days. Thanks in Advance, Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to PasteSpecial to a Different Worksheet?
In a subroutine, I'd try something like:
Option Explicit Sub testme02() Dim Shp As Shape Dim NewShp As Shape Dim DestCell As Range Set Shp = Worksheets("sheet1").Shapes("Rectangle 1") Set DestCell = Worksheets("Sheet2").Range("c3") Shp.Copy With DestCell.Parent .Paste Set NewShp = .Shapes(.Shapes.Count) End With With NewShp .Top = DestCell.Top .Left = DestCell.Left End With End Sub Just a note. I remember some posts (maybe from Jon Peltier???) that used a UDF to create sparkline charts using this kind of UDF. I thought that this was a pretty neat idea--but when I tried the UDF, excel would crash more often than not. If that's what you're doing, you may want to wait for xl2010 <vbg or save often (just in case). I skimmed Jon's site: http://peltiertech.com/Excel/Charts/ but couldn't find those UDFs. You may have better luck. EricG wrote: I have a UDF that draws a guage on the worksheet it is embedded in using common Office drawing shapes. I want to be able to copy a shape to the clipboard, and then paste it back in as a picture object. The reason I want to do this is that in Excel, text does not rotate when you rotate an object. If I convert the object to a metafile (picture), the text will rotate properly when I rotate the picture. I have an object named "Shp" which in this case is a rectangle with text in it. What I want to do is something like this: Shp.Copy ' <-- Copy the rectangle to the clipboard Set NewShp = Destination.Worksheet.PasteSpecial("Picture (Enhanced Metafile)", False, False) ' <-- Paste into a new object to manipulate "Destination" is the address of the cell containing the UDF, so "Destination.Worksheet" points to the worksheet on which the UDF is doing the drawing. It is not necessarily the active sheet, so I can't use "ActiveSheet.PasteSpecial" and I don't believe I can't refer to "Selection". I know the code above is wrong. Does anyone have a suggestion for how this might work? I'm working in 2003 SP3 these days. Thanks in Advance, Eric -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why need to use Selection.PasteSpecial vs myRange.PasteSpecial | Excel Programming | |||
Run-Time error'1004' PasteSpecial Method of Worksheet class failed | Excel Programming | |||
Help with PasteSpecial | Excel Programming | |||
pastespecial | Excel Programming | |||
pastespecial on a worksheet question | Excel Programming |