| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Data Range Mess
I am using the following raw data for my line chart. In this case I have 5
charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#2
|
|||
|
|||
|
Record a macro changing the range in the charts in one sheet.
you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#3
|
|||
|
|||
|
Thank you for your help - I created the macro in the first sheet tab and when
I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#4
|
|||
|
|||
|
The macro must have the first sheet's name hard-coded in it. You can
change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#5
|
|||
|
|||
|
Thank you for your help - I'm not sure what you mean by "The macro must have
the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#6
|
|||
|
|||
|
Karen -
Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#7
|
|||
|
|||
|
Thank you for offering your help - Here's my code:
Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#8
|
|||
|
|||
|
Karen -
Your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#9
|
|||
|
|||
|
Karen -
As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#10
|
|||
|
|||
|
Thanks again for your help
I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#11
|
|||
|
|||
|
Karen -
Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#12
|
|||
|
|||
|
There are 8 charts on each tab - How can I check to see what object name has
been assigned to each chart? Looking at my code, I have chart 1,4,5,6,7,8,9, & 10. If I change the object name for each chart, will I only have to change the following code lines: ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveSheet.ChartObjects("Chart 5").Activate etc... "Jon Peltier" wrote: Karen - Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#13
|
|||
|
|||
|
Karen -
The page I linked shows how to find the name: Hold Shift while selecting a chart, then look in the Name Box. Change the name by typing something new in the Name Box. If you rename a chart as "Profit Chart", you need to change the line in question to ActiveSheet.ChartObjects("Profit Chart").Activate - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: There are 8 charts on each tab - How can I check to see what object name has been assigned to each chart? Looking at my code, I have chart 1,4,5,6,7,8,9, & 10. If I change the object name for each chart, will I only have to change the following code lines: ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveSheet.ChartObjects("Chart 5").Activate etc... "Jon Peltier" wrote: Karen - Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#14
|
|||
|
|||
|
Thank you so much for stepping me through this. What you have told me to do
works great. I couldnt have done it without you. The problem was, the first tab (the one I used to create the macro) the chart objects were named Chart 1, 4, 5, 6, 7, 8, 9, and 10. On all the other sheet tabs, the chart objects were 1 thru 8. Thank you very much. I have learned a lot from this. "Jon Peltier" wrote: Karen - The page I linked shows how to find the name: Hold Shift while selecting a chart, then look in the Name Box. Change the name by typing something new in the Name Box. If you rename a chart as "Profit Chart", you need to change the line in question to ActiveSheet.ChartObjects("Profit Chart").Activate - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: There are 8 charts on each tab - How can I check to see what object name has been assigned to each chart? Looking at my code, I have chart 1,4,5,6,7,8,9, & 10. If I change the object name for each chart, will I only have to change the following code lines: ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveSheet.ChartObjects("Chart 5").Activate etc... "Jon Peltier" wrote: Karen - Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#15
|
|||
|
|||
|
Karen -
I suspected a problem with the chart names, especially last post, when you said there were eight per sheet, but one was named "Chart 9". Glad it's working. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you so much for stepping me through this. What you have told me to do works great. I couldnt have done it without you. The problem was, the first tab (the one I used to create the macro) the chart objects were named Chart 1, 4, 5, 6, 7, 8, 9, and 10. On all the other sheet tabs, the chart objects were 1 thru 8. Thank you very much. I have learned a lot from this. "Jon Peltier" wrote: Karen - The page I linked shows how to find the name: Hold Shift while selecting a chart, then look in the Name Box. Change the name by typing something new in the Name Box. If you rename a chart as "Profit Chart", you need to change the line in question to ActiveSheet.ChartObjects("Profit Chart").Activate - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: There are 8 charts on each tab - How can I check to see what object name has been assigned to each chart? Looking at my code, I have chart 1,4,5,6,7,8,9, & 10. If I change the object name for each chart, will I only have to change the following code lines: ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveSheet.ChartObjects("Chart 5").Activate etc... "Jon Peltier" wrote: Karen - Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#16
|
|||
|
|||
|
I have one more question - For some reason, I'm unable to change the name of
each chart on the first tab ONLY. I followed the instructions by using the name box and the chart name does not change. It's the first tab only. Any ideas? Thanks "Jon Peltier" wrote: Karen - I suspected a problem with the chart names, especially last post, when you said there were eight per sheet, but one was named "Chart 9". Glad it's working. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you so much for stepping me through this. What you have told me to do works great. I couldnt have done it without you. The problem was, the first tab (the one I used to create the macro) the chart objects were named Chart 1, 4, 5, 6, 7, 8, 9, and 10. On all the other sheet tabs, the chart objects were 1 thru 8. Thank you very much. I have learned a lot from this. "Jon Peltier" wrote: Karen - The page I linked shows how to find the name: Hold Shift while selecting a chart, then look in the Name Box. Change the name by typing something new in the Name Box. If you rename a chart as "Profit Chart", you need to change the line in question to ActiveSheet.ChartObjects("Profit Chart").Activate - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: There are 8 charts on each tab - How can I check to see what object name has been assigned to each chart? Looking at my code, I have chart 1,4,5,6,7,8,9, & 10. If I change the object name for each chart, will I only have to change the following code lines: ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveSheet.ChartObjects("Chart 5").Activate etc... "Jon Peltier" wrote: Karen - Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#17
|
|||
|
|||
|
Karen -
The sheet has no protection, does it? Is it easy enough to make a copy of the sheet, rename the charts on the copied sheet, and delete the original? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: I have one more question - For some reason, I'm unable to change the name of each chart on the first tab ONLY. I followed the instructions by using the name box and the chart name does not change. It's the first tab only. Any ideas? Thanks "Jon Peltier" wrote: Karen - I suspected a problem with the chart names, especially last post, when you said there were eight per sheet, but one was named "Chart 9". Glad it's working. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you so much for stepping me through this. What you have told me to do works great. I couldnt have done it without you. The problem was, the first tab (the one I used to create the macro) the chart objects were named Chart 1, 4, 5, 6, 7, 8, 9, and 10. On all the other sheet tabs, the chart objects were 1 thru 8. Thank you very much. I have learned a lot from this. "Jon Peltier" wrote: Karen - The page I linked shows how to find the name: Hold Shift while selecting a chart, then look in the Name Box. Change the name by typing something new in the Name Box. If you rename a chart as "Profit Chart", you need to change the line in question to ActiveSheet.ChartObjects("Profit Chart").Activate - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: There are 8 charts on each tab - How can I check to see what object name has been assigned to each chart? Looking at my code, I have chart 1,4,5,6,7,8,9, & 10. If I change the object name for each chart, will I only have to change the following code lines: ActiveSheet.ChartObjects("Chart 1").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveSheet.ChartObjects("Chart 5").Activate etc... "Jon Peltier" wrote: Karen - Does the active sheet have a chart object named "Chart 9"? How many charts are there on each worksheet? If it's only one, you can use ActiveSheet.ChartObjects(1).Activate for them all. Otherwise you can give them all descriptive names, to make it easier to know which one you are calling: http://peltiertech.com/Excel/ChartsH...ameAChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thanks again for your help I changed all the code you mentioned and I ran the macro and I'm getting a runtime error - It highlights the following code: ActiveSheet.ChartObjects("Chart 9").Activate What have I done wrong? Below is the completed code for my macro after the changes. Sub Update() ' ' Update Macro ' Macro recorded 7/11/2005 by Karen A. Whalen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-9 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B4:I4"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:I6"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=6 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B7:I7"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=21 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B9:I9"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=27 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B10:I10"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=30 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=ActiveSheet.Range("B11:I11"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=42 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-48 ActiveChart.SetSourceData Source:=ActiveSheet.Range("B14:I14"), _ PlotBy:=xlRows ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").SmallScroll Down _ :=-18 ActiveWindow.Visible = False Windows("GRAPHS FOR ALL HOSPITALS-FOR 8 YEARS 1998-2005.xls").Activate Range("A1").Select End Sub "Jon Peltier" wrote: Karen - As I suspected, your macro-recorded code has the worksheet name built into the macro: ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" You can make the statement above refer to the active sheet like this: ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("B2:I2") Also, change this statement: ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows to this: ActiveChart.SetSourceData Source:=ActiveSheet.Range("B5:I5"), _ PlotBy:=xlRows and make similar changes in the rest of your code. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for offering your help - Here's my code: Sub Update() ' ' Update Macro ' Macro recorded 7/10/2005 by Karen ' ' ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(2).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(3).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(4).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(5).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(6).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(7).XValues = "=ALEXIAN!R2C2:R2C9" ActiveChart.SeriesCollection(8).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 4").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B5:I5"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 5").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B6:I6"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 6").Activate ActiveChart.ChartArea.Select ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B7:I7"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=13 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 7").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-12 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B9:I9"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R9C2:R9C9" Windows("HospitalGraphs.xls").SmallScroll Down:=24 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-36 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B10:I10"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=32 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 9").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-30 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B11:I11"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=39 ActiveWindow.Visible = False Windows("HospitalGraphs.xls").Activate ActiveSheet.ChartObjects("Chart 10").Activate ActiveChart.ChartArea.Select Windows("HospitalGraphs.xls").SmallScroll Down:=-42 ActiveChart.SetSourceData Source:=Sheets("ALEXIAN").Range("B14:I14"), PlotBy _ :=xlRows ActiveChart.SeriesCollection(1).XValues = "=ALEXIAN!R2C2:R2C9" Windows("HospitalGraphs.xls").SmallScroll Down:=-18 End Sub "Jon Peltier" wrote: Karen - Sorry, I didn't mean you must (as in 'thou shalt') hard-code it, I meant that it's likely to already be hard-coded, based on your description and on how the macro recorder does things. Post your code and someone here will have a crack at it. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#18
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Data Range Mess
"Karen" wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
|
#19
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
Data Range Mess
Sorry, I didn't mean that the macro had to have the sheet's name hard coded
in order to work. I meant that the recorded macro has the first sheet's name hard-coded in it, because recorded macros record exactly what you do. In my post, I suggested changing the place in the macro with this hard coded sheet name, something like Worksheets("Sheet1") to ActiveSheet - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "lisapizza" wrote in message ... "Karen" wrote: Thank you for your help - I'm not sure what you mean by "The macro must have the first sheet's name hard-coded in it." How should I do the hard-coding? This is what I have to do: I click on each chart and go to Chart Source Data Select the source data up to the current year Then in the Source Data dialog box, I click the series tab and select the Category (x) axis labels Please help me with this - I have to have this done by Monday Thank you "Jon Peltier" wrote: The macro must have the first sheet's name hard-coded in it. You can change it to the active sheet: For example, change this Worksheets("Sheet1").Range("A1:B2") or this Worksheets(1).Range("A1:B2") to this ActiveSheet.Range("A1:B2") - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Karen wrote: Thank you for your help - I created the macro in the first sheet tab and when I run it for the 2nd tab, the 3rd tab and so on - It applies the data from the first tab. What am I doing wrong? Thank you "bj" wrote: Record a macro changing the range in the charts in one sheet. you may have to change it a little, but should able to just run the macro on each sheet to change all of the ranges. "Karen" wrote: I am using the following raw data for my line chart. In this case I have 5 charts on each sheet tab. One for Bolts, one for Nuts and so on.. I also have 25 sheet tabs for each customer. The way it's set up now is that the line chart only covers the data range of year 2002, 2003 & 2004. When I want to add each year I have to go into 5 charts on each tab and 25 tabs for each customer to select that data range. Initially, It should have been set up to highlight the data range up an until year 2008 so I could just plug in the numbers for each year and that data would populate the chart. Should I just go into each chart and each tab and highlight the data until 2008 or is there an easier way to do this so I don't have to do all that highlighting? Thank you, Karen 2002 2003 2004 2005 2006 2007 2008 Bolts 2145 2500 3245 Nuts 5214 5628 5487 Screws 526 650 698 Elbows 457 487 412 Nails 1248 1348 1578 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) | |||
| data range | Excel Discussion (Misc queries) | |||
| Formula Changes when data entered in referenced range | Excel Discussion (Misc queries) | |||
| Help with data not getting plotted | Excel Discussion (Misc queries) | |||
| Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel | |||
Linear Mode
