Help to write Macro to manipulate charts in workbook!

[deve8ore]

Hello,

I receive a workbook via 3rd party containing graphs within each sheet. The workbook may contain a different number of sheets per file.

I would like to write a macro to go through each sheet in the workbook (except for sheets 1 and 2), and change the name of the series to "Total Membership". Code I've tried to write is as such:

Sub Change ()

Dim ws As Worksheet
Workbooks.Open [NameofFile]
Application.Scr eenUpdating = False
For Each ws In ActiveWorkbook. Sheets
ActiveSheet.Cha rtObjects("Char t 1").Activate
ActiveChart.Plo tArea.Select
Application.Cut CopyMode = False
ActiveChart.Ser iesCollection(3 ).Name = "=""Total_Membe rship"""
ActiveWindow.Vi sible = False
If ws.Index <> 1 And ws.Index <> 2 Then ws.Select False
Next ws

End Sub


This code appears to go through each sheet in the file, but will only change the name of the first chart (in sheet 3), and do nothing to any other chart.

Yes, I am a novice... so any help would be appreciated!!!

Thank you,
Matt
Application.Scr eenUpdating = True

[jeffstl]

Maybe this?

Sub Change ()
 
Dim ws As Worksheet
Workbooks.Open [NameofFile]
Application.ScreenUpdating = False
dim x as integer
x = 2
For Each ws In ActiveWorkbook.Sheets
     x = x+1
     ActiveSheet.ChartObjects("Chart 1").Activate
     ActiveChart.PlotArea.Select
     Application.CutCopyMode = False
     ActiveChart.SeriesCollection(x).Name = "=""Total_Membership"""
     ActiveWindow.Visible = False
     If ws.Index <> 1 And ws.Index <> 2 Then ws.Select False
Next ws
 
End Sub
 

This will change the active chart index number as you loop through, instead of it always being 3.

I must admit though I am not that familiar with Excel macros such as this but that basic premise should be correct

[deve8ore]

I appreciate your help... it actually guided me in the right direction.

The "3" in the code: ActiveChart.Ser iesCollection(3 ) actually represented the number of the series I wanted to change (to name "Total Membership"), so that remains constant. The correct code was:

For Each ws In ActiveWorkbook. Sheets
x = x + 1
ActiveSheet.Cha rtObjects("Char t 1").Activate
ActiveChart.Plo tArea.Select
Application.Cut CopyMode = False
ActiveChart.Ser iesCollection(3 ).Name = "=""Total_Membe rship"""
ActiveWindow.Vi sible = False
If ws.Index <> 1 And ws.Index <> 2 Then ws.Select False
If ws.Index > 2 Then ws.Select True
Next ws


Again, thank you for your help, it is appreciated!