Dinamic charts in many sheets in one workbook excel 2003 vba

[cometorta]

Hi:

I have the code below almost finished (there are 2 macros the first one , Macro4, does the charting perfectly in sheet1) and the second macro, Macro 5, makes the macro 4 repeat itself in each sheet of my workbook except for the first sheet.

However when I attempted to run the macro 5 I get the following error message:

run -time error 1004 : unable to set the XValues
Property of the Series Class



and the line

ActiveChart.SeriesCollection(1).XValues = "=" & m & "!R9C2:R9C" & LastColPtr

in Macro 4 is highlighted in yelow in the vbe when debugging

Can someone please advice on how to proceed.

Thanks

Francisco




Sub Chart4()
Dim StartRow As Integer
Dim StartColPtr As Integer

Dim EndRow As Integer
Dim LastColPtr As Integer
Dim m As String
m = ActiveSheet.Name
StartRow = 13
EndRow = 13
StartColPtr = 2
'EndRow = Sheets("sheet1").Range("B12").CurrentRegion.Rows.Count
'LastColPtr = Sheets("sheet1").Range("B12").CurrentRegion.Columns.Count
LastColPtr = ActiveSheet.Range("B12").CurrentRegion.Columns.Count

Range(Cells(StartRow, StartColPtr), Cells(EndRow, LastColPtr)).Select
Charts.Add

ActiveChart.ChartType = xlColumnClustered

' ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B13:G13"), PlotBy _
:=xlRows
ActiveChart.PlotBy = xlRows

'x axis volatile because + - quaters
'ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R9C2:R9C7"
'ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R9C2:R9C" & LastColPtr
ActiveChart.SeriesCollection(1).XValues = "=" & m & "!R9C2:R9C" & LastColPtr





'constant for the label in column 1
'ActiveChart.SeriesCollection(1).Name = "=Sheet1!R13C1"
ActiveChart.SeriesCollection(1).Name = "=" & m & "!R13C1"

ActiveChart.Location Where:=xlLocationAsObject, Name:="" & m & ""
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "CHART NAMES 1"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = False
ActiveSheet.Shapes("Chart 1").IncrementLeft 525.75
ActiveSheet.Shapes("Chart 1").IncrementTop -15#
ActiveWindow.Visible = False

Range("A1").Select

End Sub

Sub Chart5()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Index > 1 Then 'skip the leftmost sheet
ws.Activate

Call Chart4
End If
Next ws
End Sub

 

[cometorta]

Hi all,

I think I found the problem and now i need the solution:

I relabelled the sheet names to sheet1 to sheet4 from MKT PRODUCT1 to MKT PRODUCT4. Please note my original sheet names have an empty space between them and are all caps.

Is there away to force the macro to take these NAMES?


thanks,

Francisco