Hi,

I currently have a number of graphs (10-12) that I need to replicate (for
different data obviously) across 14 worksheets (approx 140-150 graphs in
total). I am copying the relevant worksheet into the different work books
and amending the source range, however, I have noticed that the data values
do not automatically update (even when the rest of the graph does). So I can
have a bar chart showing one figure and the data label saying another.

Does anyone know of updating the data values automatically i.e. to reflect
the data point that the data value is representing? If not, Ill have to go
through each graph individually which probably wont be feasible.

Any help much appreciated!

R

Sounds like you are using Copy&Paste. The chart will still be pointing to
the original data.

Open the workbooks; use Window | Arrange to view them side by side
Holding down CTRL, drag the tab of the source worksheet to the target
workbook
Now you have an exact replica of the source worksheet and the charts use the
data on the replica


Alternatively: click on each data series in the chart, look in the formula
bar and you will see something like
=SERIES(,,[Book1]Sheet1!$A$1:$A$7,1); Edit this to remove [Book1] and change
Sheet! to the name of the current worksheet. Now the chart is linked to its
current worksheet in the current workbook

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"kippers" wrote in message
...
Hi,

I currently have a number of graphs (10-12) that I need to replicate (for
different data obviously) across 14 worksheets (approx 140-150 graphs in
total). I am copying the relevant worksheet into the different work books
and amending the source range, however, I have noticed that the data
values
do not automatically update (even when the rest of the graph does). So I
can
have a bar chart showing one figure and the data label saying another.

Does anyone know of updating the data values automatically i.e. to reflect
the data point that the data value is representing? If not, I'll have to
go
through each graph individually which probably won't be feasible.

Any help much appreciated!

R

.... or automatize the second method proposed by Bernard with the following
macro:

Sub CurveToActiveSheet()
'Sub transfers the selected series from a chart that has been copied
'from another workbook/worksheet to the data source in the same position
'as they were in the original worksheet.
'PBezucha, 2007
Dim F As String, P1 As Long, P2 As Long, Ser As Variant
Set Ser = Selection
On Error GoTo ErrExit
F = Ser.Formula
P1 = InStrRev(F, ",", -1)
P2 = InStrRev(F, "'", P1)
If P2 = 0 Then P2 = InStrRev(F, "!", P1)
P1 = InStrRev(F, "'", P2 - 1)
If P1 = 0 Then P1 = InStrRev(F, ",", P2)
Ser.Formula = Replace(F, Mid(F, P1 + 1, P2 - P1 - 1), _
ActiveSheet.Name, 1, 3)
On Error GoTo 0
Exit Sub
ErrExit:
MsgBox "No chart series has been selected"
End Sub

Regards
--
Petr Bezucha


"kippers" wrote:

Hi,

I currently have a number of graphs (10-12) that I need to replicate (for
different data obviously) across 14 worksheets (approx 140-150 graphs in
total). I am copying the relevant worksheet into the different work books
and amending the source range, however, I have noticed that the data values
do not automatically update (even when the rest of the graph does). So I can
have a bar chart showing one figure and the data label saying another.

Does anyone know of updating the data values automatically i.e. to reflect
the data point that the data value is representing? If not, Ill have to go
through each graph individually which probably wont be feasible.

Any help much appreciated!

R

Hi,

Thanks very much for the responses- really helpful.

I've changed the source string to reflect the new worksheet and the graphics
within the chart have updated fine to reflect the new data. However, the
data label (category name and value) still reflects the old data (even though
the actual bar graphic on the bar chart points to the new data value). If I
deselect the data values and then reselect them they do update, but this will
take me ages to do for 10-12 data points on 50-60 graphs!

Does anyone know if there is there any way round this?

Any help gratefully appreciated!

R

"PBezucha" wrote:

... or automatize the second method proposed by Bernard with the following
macro:

Sub CurveToActiveSheet()
'Sub transfers the selected series from a chart that has been copied
'from another workbook/worksheet to the data source in the same position
'as they were in the original worksheet.
'PBezucha, 2007
Dim F As String, P1 As Long, P2 As Long, Ser As Variant
Set Ser = Selection
On Error GoTo ErrExit
F = Ser.Formula
P1 = InStrRev(F, ",", -1)
P2 = InStrRev(F, "'", P1)
If P2 = 0 Then P2 = InStrRev(F, "!", P1)
P1 = InStrRev(F, "'", P2 - 1)
If P1 = 0 Then P1 = InStrRev(F, ",", P2)
Ser.Formula = Replace(F, Mid(F, P1 + 1, P2 - P1 - 1), _
ActiveSheet.Name, 1, 3)
On Error GoTo 0
Exit Sub
ErrExit:
MsgBox "No chart series has been selected"
End Sub

Regards
--
Petr Bezucha


"kippers" wrote:

Hi,

I currently have a number of graphs (10-12) that I need to replicate (for
different data obviously) across 14 worksheets (approx 140-150 graphs in
total). I am copying the relevant worksheet into the different work books
and amending the source range, however, I have noticed that the data values
do not automatically update (even when the rest of the graph does). So I can
have a bar chart showing one figure and the data label saying another.

Does anyone know of updating the data values automatically i.e. to reflect
the data point that the data value is representing? If not, Ill have to go
through each graph individually which probably wont be feasible.

Any help much appreciated!

R

Dear R,

There is no optimum access to this problem generally. The actualization of
categories and labels is no easy job. Still, you stimulated me to accomplish
something that I had been planning long ago, just because I need it myself.

It is advantageous here to come out from user-define chart template, which
you can embroider with all chart format details. You must, first of all,
create such a chart on base of one of your worksheets, and declare it as
user-defined one, under a proper name. The job of pasting the chart into a
worksheet with data is to be done with a further, following macro.

The macro supposes the data in individual worksheets are arranged invariably
into the same columns. These columns must be preset as ValCol, CatCol values
(and LabelCol - if the labels do exist) in the code. The first data row must
be preset too; note, however, that the data count in the worksheet can now be
variable. The last thing is setting the name of your chart template.

The macro respects the chart type; different sorts require different
treatment.

After all the adaptation, simply deploy the macro within each data worksheet
activated.


Option Explicit

Sub AddUserChart()

'Petr Bezucha, 2009

Dim ASName As String, RCFormula As String, ValSource As Range, _
ValCol As Long, CatCol As Long, LabelCol As Long, FirstRow As Long, _
LastRow As Long, ChType As Long, UserChartName As String, _
S As Worksheet, I As Long

'(manual) declaration of:
ValCol = 2 'column with values
CatCol = 1 'column with categories (or X-values)
LabelCol = 3 'column with labels (if exist)
FirstRow = 1 'first row of values

UserChartName = "MyUserChartName"

ASName = ActiveSheet.Name
Set S = Sheets(ASName)
LastRow = Cells(FirstRow, ValCol).End(xlDown).Row
Charts.Add
With ActiveChart
.ApplyCustomType ChartType:=xlUserDefined, TypeName:=UserChartName
Select Case .ChartType
Case xlXYScatter, xlXYScatterLines, xlXYScatterLinesNoMarkers, _
xlXYScatterSmooth, xlXYScatterSmoothNoMarkers
Set ValSource = Union(Range(S.Cells(FirstRow, CatCol), _
S.Cells(LastRow, CatCol)), _
Range(S.Cells(FirstRow, ValCol), S.Cells(LastRow, ValCol)))
.SetSourceData Source:=ValSource
.Location Whe=xlLocationAsObject, Name:=ASName
If ActiveChart.SeriesCollection(1).HasDataLabels Then
For I = 1 To LastRow - FirstRow + 1
ActiveChart.SeriesCollection(1).DataLabels(I).Text = _
S.Cells(FirstRow + I - 1, LabelCol).Value
Next I
End If
Case Else
Set ValSource = Range(S.Cells(FirstRow, ValCol), _
S.Cells(LastRow, ValCol))
.SetSourceData Source:=ValSource
.Location Whe=xlLocationAsObject, Name:=ASName
RCFormula = "C" & CStr(CatCol)
RCFormula = "=" & ASName & _
"!R" & CStr(FirstRow) & RCFormula & ":R" & CStr(LastRow) _
& RCFormula
ActiveChart.SeriesCollection(1).XValues = RCFormula
With ActiveChart.SeriesCollection(1).DataLabels
If .ShowValue Then .ShowValue = True
If .ShowCategoryName Then .ShowCategoryName = True
End With
End Select
End With
End Sub

--
Petr Bezucha


"kippers" wrote:

Hi,

Thanks very much for the responses- really helpful.

I've changed the source string to reflect the new worksheet and the graphics
within the chart have updated fine to reflect the new data. However, the
data label (category name and value) still reflects the old data (even though
the actual bar graphic on the bar chart points to the new data value). If I
deselect the data values and then reselect them they do update, but this will
take me ages to do for 10-12 data points on 50-60 graphs!

Does anyone know if there is there any way round this?

Any help gratefully appreciated!

R

"PBezucha" wrote:

... or automatize the second method proposed by Bernard with the following
macro:

Sub CurveToActiveSheet()
'Sub transfers the selected series from a chart that has been copied
'from another workbook/worksheet to the data source in the same position
'as they were in the original worksheet.
'PBezucha, 2007
Dim F As String, P1 As Long, P2 As Long, Ser As Variant
Set Ser = Selection
On Error GoTo ErrExit
F = Ser.Formula
P1 = InStrRev(F, ",", -1)
P2 = InStrRev(F, "'", P1)
If P2 = 0 Then P2 = InStrRev(F, "!", P1)
P1 = InStrRev(F, "'", P2 - 1)
If P1 = 0 Then P1 = InStrRev(F, ",", P2)
Ser.Formula = Replace(F, Mid(F, P1 + 1, P2 - P1 - 1), _
ActiveSheet.Name, 1, 3)
On Error GoTo 0
Exit Sub
ErrExit:
MsgBox "No chart series has been selected"
End Sub

Regards
--
Petr Bezucha


"kippers" wrote:

Hi,

I currently have a number of graphs (10-12) that I need to replicate (for
different data obviously) across 14 worksheets (approx 140-150 graphs in
total). I am copying the relevant worksheet into the different work books
and amending the source range, however, I have noticed that the data values
do not automatically update (even when the rest of the graph does). So I can
have a bar chart showing one figure and the data label saying another.

Does anyone know of updating the data values automatically i.e. to reflect
the data point that the data value is representing? If not, Ill have to go
through each graph individually which probably wont be feasible.

Any help much appreciated!

R