Hi Chris,
I will answer in reverse order;
3) The worksheet is already based on dynamic ranges. If you add months or
data rows the range will extend automatically. Look at menu item <Insert -
Name - Define> to see the xAxis named range and see how this is defined
dynamically.
2) There are two charts on Sheet1. Click on a cell in row 9 (containing
months) to show the hidden month chart, click on a blue shaded cell in
column A to show the row chart. To change the default chart, show the row
chart, then use the chart menu ,Chart - Chart Type> to change the chart to
an XY Scatter. Save the workbook. Done.
1) The code BuildChartSheets needs the following procedures to run;
ShowCht
SetChartSeries
FirstBlue
Ed Ferrero
http://edferrero.m6.net
>>>Hi Ed,
Thank you very much for your link. I found your page before I posted but
didn't think about the Multiple Chart Builder because of the gif-only
capabilities (at the time). Anyway, like the VBA macro & have some follow
questions (if you have the time):
1) To have the charts appear on unique sheets, does the Build as Unique
Sheets macro only need the code at the bottom of the email?
2) If so (or not), is there a way to somehow change the VBA code so that the
default chart type is XY Scatter instead of Column?
3) Looking ahead, would there be a way to dynamically name a range in the
VBA code, so if I added new months, the charts would automatically update?
This question is more out of curiosity as I would have no problem deleting
the old tables & re-running the macro to get the result I need. Still saves
a lot of time...
Thank you again for your time and help,
Chris
----
This part for sure?
Sub BuildChartSheets()
Dim cell As Range
Dim sPath As String
Dim sName As String
Dim sSheet As String
Application.ScreenUpdating = False
With ActiveSheet
sSheet = .Name
For Each cell In Intersect(.Range("A:A"), .UsedRange)
If cell.Font.Bold = True And cell.Interior.ColorIndex = 15 Then
Call ShowCht(ActiveSheet, cell)
sName = cell.Value
..Shapes("chtDim").Copy
..Paste
' ActiveChart.Paste
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=sName
Worksheets(sSheet).Activate
End If
Next
End With
End Sub
--------
And maybe this part?
Function FirstBlue(tgt As Range) As Range ' find the first blue shaded cell
above tgt
Set FirstBlue = tgt
Do Until FirstBlue.Interior.ColorIndex = 37
Set FirstBlue = FirstBlue.Offset(-1, 0)
If FirstBlue.Row = 1 Then Exit Do
Loop
End Function
Similar Threads
