+ Reply to Thread
Results 1 to 19 of 19

Data Range Mess

Hybrid View

  1. 07-06-2005, 12:05 PM #1
    bj
    Guest

    RE: Data Range Mess

    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 With QuoteRegister To Reply
  2. 07-08-2005, 04:05 PM #2
    Karen
    Guest

    RE: Data Range Mess

    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 With QuoteRegister To Reply
  3. 07-08-2005, 08:05 PM #3
    Jon Peltier
    Guest

    Re: Data Range Mess

    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 With QuoteRegister To Reply
  4. 07-10-2005, 12:05 AM #4
    Karen
    Guest

    Re: Data Range Mess

    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 With QuoteRegister To Reply
  5. 07-10-2005, 01:05 AM #5
    Jon Peltier
    Guest

    Re: Data Range Mess

    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
    >>>>>
    >>
    Reply With QuoteRegister To Reply
  6. 07-10-2005, 09:05 PM #6
    Karen
    Guest

    Re: Data Range Mess

    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
    > >>>>>
    > >>
    >
    Reply With QuoteRegister To Reply
  7. 07-11-2005, 10:05 AM #7
    Jon Peltier
    Guest

    Re: Data Range Mess

    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
    >>>>>>>
    >>>>
    Reply With QuoteRegister To Reply
  8. 07-11-2005, 10:05 AM #8
    Jon Peltier
    Guest

    Re: Data Range Mess

    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
    >>>>>>>
    >>>>
    Reply With QuoteRegister To Reply
  9. 01-12-2006, 05:25 PM #9
    lisapizza
    Guest

    Re: 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
    > > >>>
    > >
    Reply With QuoteRegister To Reply
  10. 01-14-2006, 10:40 AM #10
    Jon Peltier
    Guest

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


    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1