1 ChartSheet, 10 worksheets

xl 2000

I currently have 2 embedded charts on each of 10 wkshts ( R1 to R10). I have
my own toolbar that jumps to each chart with buttons G1 and G2.

I would like to have 1 chart sheet at the back of the book and when I press
G1 or G2 it will take me to the chart sheet and display the correct data for
the active sheet.

For instance, if I am on sheet R1 and I press G1 xl will jump to the chart
sheet and display the data in ='R1'!$T$7:$AB$7. This is the ref for one
line. The number of lines varies from one wkshts to the next and from day to
day(I have a wkbk for every day of the year). The range G1 uses is S6 to
AB30. This is constant across all the wkshts.

Any help would be greatly appreciated.
Ta,
Martin

I have been tried the code below. But this refers to a button in the
worksheet. I would much prefer to use the buttons in my toolbar
Ta,
Martin
Private Sub CommandButton1_Click()
Chart1.Select
ActiveChart.SetSourceData Source:=Sheets("R1").Range("S6:AB30")
End Sub

"Martin Wheeler" <[email protected]> wrote in message
news:[email protected]...
> xl 2000
>
> I currently have 2 embedded charts on each of 10 wkshts ( R1 to R10). I
have
> my own toolbar that jumps to each chart with buttons G1 and G2.
>
> I would like to have 1 chart sheet at the back of the book and when I
press
> G1 or G2 it will take me to the chart sheet and display the correct data
for
> the active sheet.
>
> For instance, if I am on sheet R1 and I press G1 xl will jump to the chart
> sheet and display the data in ='R1'!$T$7:$AB$7. This is the ref for one
> line. The number of lines varies from one wkshts to the next and from day
to
> day(I have a wkbk for every day of the year). The range G1 uses is S6 to
> AB30. This is constant across all the wkshts.
>
> Any help would be greatly appreciated.
> Ta,
> Martin
>
>

Martin -

The same code would work from a command bar button. Put the code into a regular code
module, not a worksheet class module.

Sub ActivateChart1()
Chart1.Select
ActiveChart.SetSourceData Source:=Sheets("R1").Range("S6:AB30")
End Sub

Add a button to an Excel command bar: Tools menu, Customize, Commands tab, scroll
down to Macros in the left, and drag the button on the right to the command bar.
Assign the macro: Tools menu, Customize, then right click on the button, choose
Assign Macro, and select your macro name from the list.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Martin Wheeler wrote:

> I have been tried the code below. But this refers to a button in the
> worksheet. I would much prefer to use the buttons in my toolbar
> Ta,
> Martin
> Private Sub CommandButton1_Click()
> Chart1.Select
> ActiveChart.SetSourceData Source:=Sheets("R1").Range("S6:AB30")
> End Sub
>
> "Martin Wheeler" <[email protected]> wrote in message
> news:[email protected]...
>
>>xl 2000
>>
>>I currently have 2 embedded charts on each of 10 wkshts ( R1 to R10). I
>
> have
>
>>my own toolbar that jumps to each chart with buttons G1 and G2.
>>
>>I would like to have 1 chart sheet at the back of the book and when I
>
> press
>
>>G1 or G2 it will take me to the chart sheet and display the correct data
>
> for
>
>>the active sheet.
>>
>>For instance, if I am on sheet R1 and I press G1 xl will jump to the chart
>>sheet and display the data in ='R1'!$T$7:$AB$7. This is the ref for one
>>line. The number of lines varies from one wkshts to the next and from day
>
> to
>
>>day(I have a wkbk for every day of the year). The range G1 uses is S6 to
>>AB30. This is constant across all the wkshts.
>>
>>Any help would be greatly appreciated.
>>Ta,
>>Martin
>>
>>
>
>
>

Hi Jon,
Thanks, I've tried it and it works.
Ta,
Martin


"Jon Peltier" <[email protected]> wrote in message
news:[email protected]...
> Martin -
>
> The same code would work from a command bar button. Put the code into a
regular code
> module, not a worksheet class module.
>
> Sub ActivateChart1()
> Chart1.Select
> ActiveChart.SetSourceData Source:=Sheets("R1").Range("S6:AB30")
> End Sub
>
> Add a button to an Excel command bar: Tools menu, Customize, Commands tab,
scroll
> down to Macros in the left, and drag the button on the right to the
command bar.
> Assign the macro: Tools menu, Customize, then right click on the button,
choose
> Assign Macro, and select your macro name from the list.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Martin Wheeler wrote:
>
> > I have been tried the code below. But this refers to a button in the
> > worksheet. I would much prefer to use the buttons in my toolbar
> > Ta,
> > Martin
> > Private Sub CommandButton1_Click()
> > Chart1.Select
> > ActiveChart.SetSourceData Source:=Sheets("R1").Range("S6:AB30")
> > End Sub
> >
> > "Martin Wheeler" <[email protected]> wrote in message
> > news:[email protected]...
> >
> >>xl 2000
> >>
> >>I currently have 2 embedded charts on each of 10 wkshts ( R1 to R10). I
> >
> > have
> >
> >>my own toolbar that jumps to each chart with buttons G1 and G2.
> >>
> >>I would like to have 1 chart sheet at the back of the book and when I
> >
> > press
> >
> >>G1 or G2 it will take me to the chart sheet and display the correct data
> >
> > for
> >
> >>the active sheet.
> >>
> >>For instance, if I am on sheet R1 and I press G1 xl will jump to the
chart
> >>sheet and display the data in ='R1'!$T$7:$AB$7. This is the ref for one
> >>line. The number of lines varies from one wkshts to the next and from
day
> >
> > to
> >
> >>day(I have a wkbk for every day of the year). The range G1 uses is S6
to
> >>AB30. This is constant across all the wkshts.
> >>
> >>Any help would be greatly appreciated.
> >>Ta,
> >>Martin
> >>
> >>
> >
> >
> >
>