Plots of filtered data RRS feed

  • Question

  • Hi all,

    I have data in excel 2007 that is filtered. I want from the filtered data to create plots. The first plot is fine but then they all takes the same values as the criteria for the last filtration. How can I with VBA cut the connection to the source data so the old plots does not change when the filtration does? (I do not want to make my charts as pictures.)

    Thankful for help with this.



    • Edited by JS3 Monday, February 13, 2012 10:24 AM
    Monday, February 13, 2012 10:23 AM


  • If you are using VBA probably the easiest way is to copy the filtered data elsewhere, and use that as the source data. Not sure if you want to make a new chart each time, which would also mean copying to a new location each time, or updating the existing chart. This example to do the latter (assumes filtered data on Sheet1 and an existing chart on Sheet2)

    Sub test()
    Dim rngFltr As Range, rngDest As Range
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Set rngFltr = Worksheets("Sheet1").AutoFilter.Range
    Set rngDest = Worksheets("Sheet2").Range("A1")
    rngFltr.Copy rngDest
    Set rngDest = rngDest.CurrentRegion
    Set cht = Worksheets("Sheet2").ChartObjects(1).Chart
    cht.SetSourceData rngDest
    End Sub

    As written SetSourceData assumes the data is conventionally laid out, otherwise would need to update category or X values and each series individually. Also use of CurrentRegion above is quick & dirty, would need more work to ensure no old data is included. Anyway, adapt as required.

    Peter Thornton

    • Marked as answer by JS3 Wednesday, February 15, 2012 8:42 AM
    Monday, February 13, 2012 1:47 PM