copy chart from one sheet to other.

[musa.biralo]

Hi,
i have one chart on each sheet. i record a macro to copy chart from one
sheet to other. when i run the macro...it's taking a while to
copy....will you please help me to make it faster. my other problem is
to paste the chart at specific x,y or at say B5. how can i do that...

(i tried using sendkeys "^C" but its not working...for some reason...)

Worksheets("a").ChartObjects(1).Copy

Sheets("b").Select
ActiveSheet.Paste

'--------------------------------------------------

ActiveSheet.ChartObjects(1).Activate
namechart = ActiveSheet.ChartObjects(1).Name
ActiveSheet.Shapes(namechart).IncrementLeft 265.5
ActiveSheet.Shapes(namechart).IncrementTop -116.25

recording macro gave me these lines but sometime it's pasting at one
place and some time at other...
is there a way to paste at specific x,y or near to cell B5?


Thanks you so much for your help.
musa.biralo

 

[Peter T]

One way -

Sub test()
Dim chobj As ChartObject

Worksheets("Sheet1").ChartObjects(2).Copy

With Worksheets("Sheet2")
.Paste
Application.CutCopyMode = False
Set chobj = .ChartObjects(.ChartObjects.Count)
With .Range("B5")
chobj.Left = .Left
chobj.Top = .Top
End With

End With
End Sub

This may leave the chartobject on Sheet2 selected. Would need to activate
Sheet2 before deselecting it.

Regards,
Peter T

 

[Guest]

Musa,

For the positioning of the chart try:

ActiveSheet.ChartObjects(namechart).Left = 10
ActiveSheet.ChartObjects(namechart).Top = 10

instead of

ActiveSheet.Shapes(namechart).IncrementLeft 265.5
ActiveSheet.Shapes(namechart).IncrementTop -116.25

regarding your copy and past I'm not sure as I don't know what your chart
looks like but it may be due to the links and the formula that you have
recalculating.

 

[musa.biralo]

Huge thanks Peter.
Positioning of the chart works great!!!

You know. the copying command....
"" Worksheets("Sheet1").ChartObjects(2).Copy""
is taking a long time...is there a way to do the same thing by putting
some filter or something smarter...i am simply trying to copy exactly
same chart over another sheet.

i did little test by doing Ctrl+C after selecting the chart...it
happens instantly....

Still thanks a lot, Peter.
musa.biralo.

 

[musa.biralo]

Martin!,
Thanks a bunch.

i will really appreciate your help. I will be pleased if you can make
the copying chart faster. i have very simple straight line chart and
just trying to copy the chart from one sheet to another in short time.
with the command i posted at the top there is a time lag of about 1-2
sec.......i know this is not a big time but still trying to get
faster...

Your help is highly appreciated.

Thanks so much.
musa.biralo

 

[Peter T]

I don't know why the copy takes so long for you particularly as you say it
works instantly with Ctrl-c, can't think why code would be any slower.

Or is it instant, how do you know when it's finally copied into the
clipboard. Occasionally I find it very slow to clear the clipboard of
previous contents after copying 100's of objects in one go, various API
methods to clear and speed up don't help.

Might be worth determining if the delay is with the copy or with the paste,
I would expect the paste to be the slower action but not a "long time".

In the example I posted add skyboxes after copy & paste, eg
Msgbox "Copied"
MsgBox "Pasted"

Run the routine from a button on the sheet with the VBE closed. Post back
which action is taking the time (not that I'll then know what to suggest!).

Regards,
Peter T