copy chart from one sheet to other.  
Author Message
RickySimons





PostPosted: Sat Dec 09 19:23:51 CST 2006 Top

Excel Programming >> copy chart from one sheet to other.

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

Excel207  
 
 
Peter





PostPosted: Sat Dec 09 19:23:51 CST 2006 Top

Excel Programming >> copy chart from one sheet to other. 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



> 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
>


 
 
martin_fishlock





PostPosted: Sat Dec 09 19:27:01 CST 2006 Top

Excel Programming >> copy chart from one sheet to other. 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.

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.




> 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
>
>
 
 
musa





PostPosted: Sat Dec 09 20:47:08 CST 2006 Top

Excel Programming >> copy chart from one sheet to other. 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.


> 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
>


> > 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
> >

 
 
musa





PostPosted: Sat Dec 09 20:50:48 CST 2006 Top

Excel Programming >> copy chart from one sheet to other. 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


> 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.
>
> --
> Hope this helps
> Martin Fishlock
> Please do not forget to rate this reply.
>
>

>
> > 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





PostPosted: Sun Dec 10 18:08:29 CST 2006 Top

Excel Programming >> copy chart from one sheet to other. 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



> 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.
>

> > 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
> >


> > > 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
> > >
>