By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,918 Members | 1,557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,918 IT Pros & Developers. It's quick & easy.

Help to write Macro to manipulate charts in workbook!

P: 34
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.ScreenUpdating = False
For Each ws In ActiveWorkbook.Sheets
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(3).Name = "=""Total_Membership"""
ActiveWindow.Visible = 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.ScreenUpdating = True
Apr 16 '08 #1
Share this Question
Share on Google+
2 Replies


jeffstl
Expert 100+
P: 432
Maybe this?

Expand|Select|Wrap|Line Numbers
  1. Sub Change ()
  2.  
  3. Dim ws As Worksheet
  4. Workbooks.Open [NameofFile]
  5. Application.ScreenUpdating = False
  6. dim x as integer
  7. x = 2
  8. For Each ws In ActiveWorkbook.Sheets
  9.      x = x+1
  10.      ActiveSheet.ChartObjects("Chart 1").Activate
  11.      ActiveChart.PlotArea.Select
  12.      Application.CutCopyMode = False
  13.      ActiveChart.SeriesCollection(x).Name = "=""Total_Membership"""
  14.      ActiveWindow.Visible = False
  15.      If ws.Index <> 1 And ws.Index <> 2 Then ws.Select False
  16. Next ws
  17.  
  18. End Sub
  19.  
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
Apr 17 '08 #2

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

The "3" in the code: ActiveChart.SeriesCollection(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.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = False
ActiveChart.SeriesCollection(3).Name = "=""Total_Membership"""
ActiveWindow.Visible = 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!
Apr 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.