How to PasteSpecial to a Different Worksheet?

[EricG]

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

 

[norie]

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.

 

[Barb Reinhardt]

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.

 

[Dave Peterson]

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]

Thanks, Dave. I'll give your suggestion a try when I have a chance.

My current project is inspired by the sparklines stuff. In fact, I have
made some contributions to this add-in:

http://sparklines-excel.blogspot.com/

Regards,

Eric