Extract, Sort and Graph Table

[atwy]

Hi,

Given a long table with various columns, how can I extract the rows based on defined column data, paste the selected into another worksheet, then sort and graph it as a scatterplot?

For Eg. with the attached spreadsheet with a list of fruits, how can the fruits that are green (watermelon and pear) be extracted, then sorted by price, and then graphed as a scatterplot (size vs price)?

Thanks in advance!

[pbengtss]

I guess you could make a pivot table/graph as attached.

[Kaper]

As you do not need sorted data for scatterplot (especially when plotted with no inteconnecting lines):
Just plot of the filtered data would do. Remember to set in properties to not change size and not move with cells.

[atwy]

Thanks for the suggestions guys!

Is there a way to automate this using VBA code? Such that with a table of data, how can Excel automatically filter and output such scatterplots based on pre-defined criteria?

The data in the table will be refreshed periodically.

Thanks!

[Kaper]

have a look on such sample macro:

Sub test()
Dim i&, allvals, myvalcol As New Collection
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False
allvals = Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
On Error Resume Next
  For i = 1 To UBound(allvals)
    myvalcol.Add allvals(i, 1), allvals(i, 1)
  Next i
On Error GoTo 0
For i = 1 To myvalcol.Count
  ActiveSheet.Range("A2").CurrentRegion.AutoFilter Field:=2, Criteria1:=myvalcol(i)
  ActiveSheet.ChartObjects(1).Activate
  ActiveChart.ChartTitle.Text = myvalcol(i)
  ActiveChart.ChartArea.Copy
  Cells(2, 6 + 8 * i).Select
  ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:= _
     False, DisplayAsIcon:=False
Next i
ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartTitle.Text = "All"
ActiveSheet.AutoFilterMode = False
Range("A3").Select
End Sub

[atwy]

Hi again Kaper, that's a wonderful piece of code. I've tried to use it but it gives a "application-defined or object-defined error". This occurs at the line "ActiveSheet.ChartObjects(1).Activate"

[Kaper]

Try 3 measures:
- add

Application.CutCopyMode = False

before

ActiveChart.ChartArea.Copy

- move your main chart slightly up to have upper left corner above the row with filter header.
- make sure all old pictures are deleted from the sheet before you start to create new. you may add such code to do so:

Dim myshp As Shape
For Each myshp In ActiveSheet.Shapes
  If myshp.Type = msoPicture Then myshp.Delete
Next myshp

[atwy]

Hi Kaper realise that a chart needs to be already created in order for this to work. It works now after I've added a chart.

It looks nice but unfortunately the data points aren't viewable as they are pasted as images. Is there a suggestion on how to create individual tables and their own charts?

Thanks again!