automating charts

[jborcher]

I have a several huge spreadsheets for which I need to automate xy
scatter plots or line plots.

column A contains a name that identifies a water well. This is text.

column B contains a date on which the water-level in the well was
measured. This is date.

column C contains the water-level measurement, that is the distance to
the water level, in feet below the ground surface. This is a number
xx.xx.

Well Name Date Water
Level
002S003W-02L022 3/14/2004 22.04
002S003W-02L022 3/15/2004 21.28
002S003W-02L022 3/16/2004 20.99
2S/3W-7A005 1/2/2004 9.32
2S/3W-7A005 2 /4/2004 2.77
2S/3W-7A005 2/15/2004 3.50
2S/3W-7A005 2/22/2004 4.20
2S/3W-7A005 3/9/2004 5.12
. .
. .
. .


I need some kind of macro or script that will search the sheet, select
the rows of data for just one well, create an xy plot of date (x-axis)
vs water level (y-axis) using whatever default x and y axis ranges Excel
chooses, put the well name in the chart title, save the chart naming it
the well name, print the chart, go back to the sheet and select all the
data from the next well in column A, plot the chart in the same manner
and continue until charts have been created for all wells in the sheet.

Within a sheet, the number of rows of data for each well varies from 3
to several hundred. Individual sheets range in size from 2000 lines to
almost 31,000 lines.

I can create a keyboard macro that will run a chart template, but I'm
unsure how to automate the iterative selection of data for subsequent
plots, chart tile, the naming of each chart.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Dave Peterson]

You may not need to do all that work.

I don't use charts all that much, but I created some test data in A1:C21. (Nice
headers in row 1.)

I put some random dates and numbers in columns B&C. I put a few different
"wells" in column A.

Then I sorted by the date field. I applied Data|Filter|autofilter to my data.

Then I created a chart and put it into a new chart sheet. I formatted it the
way I wanted--but I didn't worry about the actual picture of the graph.

When I applied a filter to the range, only those visible values were shown in
the chart.

If you see more than the visible cells, you can toggle this by:
Tools|Options|Chart Tab|Plot Visible cells only.

Note that the bottom axis did show the "well" that I had showing.

So you could create the chart sheet and then filter one-by-one and print the
resulting charts.

But if you create the first chart (on a chartsheet called Chart1), then this
might be sufficient.


Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim chtSheet As Chart
Dim curAutoFilterRange As Range

Dim myCell As Range
Dim myRng As Range

Set wks = Worksheets("sheet1")
Set chtSheet = Sheets("chart1")

If wks.AutoFilterMode = False Then
MsgBox "Please apply Data|autofilter!"
Exit Sub
End If

Set curAutoFilterRange = wks.AutoFilter.Range

Set newWks = Worksheets.Add

wks.AutoFilter.Range.Columns(1).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=newWks.Range("A1"), Unique:=True

With newWks
Set myRng = .Range("a2:a" & .Cells(.Rows.Count, "A").End(xlUp).Row)
myRng.Sort key1:=.Range("a1"), order1:=xlAscending, header:=xlNo
End With

With wks
For Each myCell In myRng.Cells
curAutoFilterRange.AutoFilter Field:=1, _
Criteria1:=myCell.Value
chtSheet.PrintOut preview:=True
Next myCell
.ShowAllData
End With

Application.DisplayAlerts = False
newWks.Delete
Application.DisplayAlerts = True

End Sub


(I used Sheet1, Chart1 as my sheet names.)