Data Range Mess

Jon Peltier · 07-08-2005, 08:05 PM

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
>>>

Karen · 07-10-2005, 12:05 AM

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
> >>>
>

Jon Peltier · 07-10-2005, 01:05 AM

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
>>>>>
>>

Karen · 07-10-2005, 09:05 PM

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
> >>>>>
> >>
>

Jon Peltier · 07-11-2005, 10:05 AM

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
>>>>>>>
>>>>

Jon Peltier · 07-11-2005, 10:05 AM

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
>>>>>>>
>>>>

Karen · 07-11-2005, 12:05 PM

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
> >>>>>>>
> >>>>
>

Jon Peltier · 07-11-2005, 12:05 PM

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
>>>>>>>>>
>>>>>>

lisapizza · 01-12-2006, 05:25 PM

"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
> > >>>
> >

Jon Peltier · 01-14-2006, 10:40 AM

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" <lisapizza@discussions.microsoft.com> wrote in message
news:1B3CAF6B-F602-4566-BC8A-55538214296A@microsoft.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
>> > >>>
>> >

Data Range Mess

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Re: Data Range Mess

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions