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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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" wrote in message
...

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