Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel 2010 - copying and pasting to an existing graph.

    I remember that at one time, I was able to add data to an existing graph (X-Y plot) by simply highlighting the appropriate new data in the appropriate columns in the data sheet and then copying them and pasting them on the graph - somehow. For example, I track some stock market indices by retrieving the date from the web and then entering it into a spreadsheet. I do this on a semi-regular basis - once every week or two, or as required. So my data consists of a DATE and a CLOSING VALUE. I have been doing this for quite some time and the columns are getting fairly long to have to remake the graph every time I update the data columns. It seems to me that there is/was a way to simply copy the "new" data and paste it to the graph and Excel would automatically update the graph. Maybe this is wishful thinking, don't know, but what I do know is that I cannot remember, or figure out, how this is done. I am wondering: 1) is this possible? and if so, 2) how is it done? Any insight or assistance that anyone can provide will be greatly appreciated.

    Ron M

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Ron,

    Here is some code I wrote for another member that updates the chart when you add additional data in the month and amount columns to the next line. Perhaps, this will help.

    Maud

    Graph1.png

    Code:
    Private Sub Worksheet_change(ByVal Target As Range)
    Dim Data As Range
    '---------------------------------------------------------------
    'DETERMINE IF SELECTED CELL IS IN RANGE
    If Target.Column <= 2 And Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" Then
        Set Data = Range("a2").CurrentRegion  'SET RANGE
        Data.Select
        Data.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes 'SORT NEW DATA
        ActiveSheet.Shapes(1).Select
        ActiveChart.SetSourceData Source:=Range(Data.Address) 'UPDATE CHART
        Cells(ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select
    End If
    End Sub
    Attached Files Attached Files

  4. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,078
    Thanks
    13
    Thanked 36 Times in 35 Posts
    When you add a new row, Maud's routine will provide an automatic update. Also, I think you'll find that if you add a new row, then click on the chart area, you can drag the blue border around the data to include your new entry.

    I removed the two January entries by (a) clicking on a blank area of the chart to select the chart area then (b) dragged the blue border around the data to not include the 2 January entries and the graph updates automatically.

    Clip0001.jpg
    Last edited by kweaver; 2013-12-15 at 15:07.

  5. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Looks like an interesting approach - VBA was never one of my strong points, but I will give it a go. My only question is will this handle the addition of more than one line at a time? I usually update the tracking spreadsheet with a week or more's data at time, sometimes, I may add a whole month's data at once.

    Also, this looks like it uses named ranges, so which columns do I have to name, or which names/variable should I change in the code to conform to my column names.

    Thanks for this.

    Ron M
    Last edited by Ron M; 2013-12-15 at 15:41.

  6. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Ron,

    The answer is yes. If you paste additional rows to column A and B, the chart will update. In the sample below, I pasted 4 entries in rows 14 through 17 and the chart updated

    Chart1.png

    Also, If you select the month then delete the value, the chart will update as well. The worksheet uses a variable for the range called Data.
    Last edited by Maudibe; 2013-12-15 at 22:31.

  7. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    Ron,

    It would be easier if you posted a sample of the worksheet and I will be happy to amend the code to your needs.

    Maud

  8. #7
    New Lounger kalvinson's Avatar
    Join Date
    Dec 2013
    Location
    The beautiful and serene Yorkshire Dales, England, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Your original premise is correct. Select and copy the data that you want to add to the chart, click on the data series on the chart so it is highlighted (this will also highlight the data in the columns being used to create the chart), click on the paste button and the data will be added to the chart.
    Last edited by kalvinson; 2013-12-16 at 05:53.

  9. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    707
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kalvinson View Post
    Your original premise is correct. Select and copy the data that you want to add to the chart, click on the data series on the chart so it is highlighted (this will also highlight the data in the columns being used to create the chart), click on the paste button and the data will be added to the chart.
    Thank you, I knew my memory would not fail me.

    Ron M

Tags for this Thread

Posting Permissions

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