+ Reply to Thread
Results 1 to 5 of 5

[SOLVED] Copying charts/data

  1. #1
    Paul Reeve
    Guest

    [SOLVED] Copying charts/data

    Hi all,

    I apologise if this has been asked before: I could not find any reference.

    I have a workbook with about 50 sheets on which I need to plot the same
    range of cells (Q49:T206) on an embedded chart in each sheet showing the
    different data in the sheets.
    If I just copy the chart & data then paste it into each of the other sheets,
    it references the original data uniquely.

    Is there a simple way of doing what I need with vba? Or do I have to change
    all 4 series on each sheet manually?

    thanks in advance for any help

    remove the obvious if replying

    Paul



  2. #2
    John Mansfield
    Guest

    RE: Copying charts/data

    Paul,

    Assuming you already have one embedded chart per sheet, try the following
    code. It uses a counter (the variable -> Cnt) to refer to the sheet number.
    The counter is incremented by 1 as each sheet is looped through e.g. Sheet1,
    Sheet2, Sheet3, Sheet4,..., Sheet50 etc. The actual names on the sheet tabs
    shouldn't matter. Place the code in a standard module to run it.

    Sub ChangeSource()
    Dim wks As Worksheet
    Dim cht As ChartObject
    Cnt = 1
    For Each wks In Worksheets
    For Each cht In wks.ChartObjects
    cht.Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets(Cnt).Range("Q49:T206")
    Next cht
    Cnt = Cnt + 1
    Next wks
    End Sub

    ----
    Regards,
    John Mansfield
    http://www.pdbook.com


    "Paul Reeve" wrote:

    > Hi all,
    >
    > I apologise if this has been asked before: I could not find any reference.
    >
    > I have a workbook with about 50 sheets on which I need to plot the same
    > range of cells (Q49:T206) on an embedded chart in each sheet showing the
    > different data in the sheets.
    > If I just copy the chart & data then paste it into each of the other sheets,
    > it references the original data uniquely.
    >
    > Is there a simple way of doing what I need with vba? Or do I have to change
    > all 4 series on each sheet manually?
    >
    > thanks in advance for any help
    >
    > remove the obvious if replying
    >
    > Paul
    >
    >
    >


  3. #3
    Jon Peltier
    Guest

    Re: Copying charts/data

    Paul -

    A quick alternative: Copy the old sheet with the data and embedded chart. Copy the
    new data, and paste it into the new sheet on top of the old data.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

    Paul Reeve wrote:

    > Hi all,
    >
    > I apologise if this has been asked before: I could not find any reference.
    >
    > I have a workbook with about 50 sheets on which I need to plot the same
    > range of cells (Q49:T206) on an embedded chart in each sheet showing the
    > different data in the sheets.
    > If I just copy the chart & data then paste it into each of the other sheets,
    > it references the original data uniquely.
    >
    > Is there a simple way of doing what I need with vba? Or do I have to change
    > all 4 series on each sheet manually?
    >
    > thanks in advance for any help
    >
    > remove the obvious if replying
    >
    > Paul
    >
    >



  4. #4
    Paul Reeve
    Guest

    Re: Copying charts/data

    thanks gents - I will try these out when I get a chance

    Paul

    "Paul Reeve" <paul@preeve.plusdotcom> wrote in message
    news:%23axqHBr8EHA.1564@TK2MSFTNGP09.phx.gbl...
    > Hi all,
    >
    > I apologise if this has been asked before: I could not find any reference.
    >
    > I have a workbook with about 50 sheets on which I need to plot the same
    > range of cells (Q49:T206) on an embedded chart in each sheet showing the
    > different data in the sheets.
    > If I just copy the chart & data then paste it into each of the other
    > sheets, it references the original data uniquely.
    >
    > Is there a simple way of doing what I need with vba? Or do I have to
    > change all 4 series on each sheet manually?
    >
    > thanks in advance for any help
    >
    > remove the obvious if replying
    >
    > Paul
    >




  5. #5
    Paul Reeve
    Guest

    Re: Copying charts/data

    Thanks, John, that worked a treat

    Paul

    "John Mansfield" <JohnMansfield@discussions.microsoft.com> wrote in message
    news:FD83C0E2-4D15-4593-A348-E92B2CD819F1@microsoft.com...
    > Paul,
    >
    > Assuming you already have one embedded chart per sheet, try the following
    > code. It uses a counter (the variable -> Cnt) to refer to the sheet
    > number.
    > The counter is incremented by 1 as each sheet is looped through e.g.
    > Sheet1,
    > Sheet2, Sheet3, Sheet4,..., Sheet50 etc. The actual names on the sheet
    > tabs
    > shouldn't matter. Place the code in a standard module to run it.
    >
    > Sub ChangeSource()
    > Dim wks As Worksheet
    > Dim cht As ChartObject
    > Cnt = 1
    > For Each wks In Worksheets
    > For Each cht In wks.ChartObjects
    > cht.Activate
    > ActiveChart.ChartArea.Select
    > ActiveChart.SetSourceData Source:=Sheets(Cnt).Range("Q49:T206")
    > Next cht
    > Cnt = Cnt + 1
    > Next wks
    > End Sub
    >
    > ----
    > Regards,
    > John Mansfield
    > http://www.pdbook.com
    >
    >
    > "Paul Reeve" wrote:
    >
    >> Hi all,
    >>
    >> I apologise if this has been asked before: I could not find any
    >> reference.
    >>
    >> I have a workbook with about 50 sheets on which I need to plot the same
    >> range of cells (Q49:T206) on an embedded chart in each sheet showing the
    >> different data in the sheets.
    >> If I just copy the chart & data then paste it into each of the other
    >> sheets,
    >> it references the original data uniquely.
    >>
    >> Is there a simple way of doing what I need with vba? Or do I have to
    >> change
    >> all 4 series on each sheet manually?
    >>
    >> thanks in advance for any help
    >>
    >> remove the obvious if replying
    >>
    >> Paul
    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1