Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the source
data. Will my pivot tables automatically incorporate new data? At the moment
I have all my data in one worksheet. I assume the easiest method of doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over 30). I
have one other pie 'slice' that is 'other' that makes up 100%. I have done
this by creating a separate table under the pivot table that references the
top 10 using an index lookup function. That works fine but I'd rather the
chart were a pivot table so that users can manipulate the data on the chart
(rather than on the table). I know that I can sort by the top 10 in the table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

1. Define a dynamic range on the pivot data worksheet, something like this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the top ten.
Offhand, without a lot of experimenting, I don't know how to get the Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the source
data. Will my pivot tables automatically incorporate new data? At the
moment
I have all my data in one worksheet. I assume the easiest method of doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over 30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I have done
this by creating a separate table under the pivot table that references
the
top 10 using an index lookup function. That works fine but I'd rather the
chart were a pivot table so that users can manipulate the data on the
chart
(rather than on the table). I know that I can sort by the top 10 in the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Thanks Jon,
that's a great help.
Have not tested yet but am sure I can follow what you've suggested

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something like this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the top ten.
Offhand, without a lot of experimenting, I don't know how to get the Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the source
data. Will my pivot tables automatically incorporate new data? At the
moment
I have all my data in one worksheet. I assume the easiest method of doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over 30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I have done
this by creating a separate table under the pivot table that references
the
top 10 using an index lookup function. That works fine but I'd rather the
chart were a pivot table so that users can manipulate the data on the
chart
(rather than on the table). I know that I can sort by the top 10 in the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something like this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the top ten.
Offhand, without a lot of experimenting, I don't know how to get the Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the source
data. Will my pivot tables automatically incorporate new data? At the
moment
I have all my data in one worksheet. I assume the easiest method of doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over 30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I have done
this by creating a separate table under the pivot table that references
the
top 10 using an index lookup function. That works fine but I'd rather the
chart were a pivot table so that users can manipulate the data on the
chart
(rather than on the table). I know that I can sort by the top 10 in the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

There's no error proofing in that procedure. The procedure assumes that the
chart is embedded in the same sheet as the pattern table, and that it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the top
ten.
Offhand, without a lot of experimenting, I don't know how to get the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the
source
data. Will my pivot tables automatically incorporate new data? At the
moment
I have all my data in one worksheet. I assume the easiest method of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I have
done
this by creating a separate table under the pivot table that references
the
top 10 using an index lookup function. That works fine but I'd rather
the
chart were a pivot table so that users can manipulate the data on the
chart
(rather than on the table). I know that I can sort by the top 10 in the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the range were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes that the
chart is embedded in the same sheet as the pattern table, and that it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the top
ten.
Offhand, without a lot of experimenting, I don't know how to get the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the
source
data. Will my pivot tables automatically incorporate new data? At the
moment
I have all my data in one worksheet. I assume the easiest method of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I have
done
this by creating a separate table under the pivot table that references
the
top 10 using an index lookup function. That works fine but I'd rather
the
chart were a pivot table so that users can manipulate the data on the
chart
(rather than on the table). I know that I can sort by the top 10 in the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

I named the worksheets Sheet1 and Sheet2. You have to use your own sheet
names.

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


"Graeme at Raptup" wrote in
message ...
Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the range
were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes that
the
chart is embedded in the same sheet as the pattern table, and that it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active
sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a
difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based
on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the
top
ten.
Offhand, without a lot of experimenting, I don't know how to get the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the
source
data. Will my pivot tables automatically incorporate new data? At
the
moment
I have all my data in one worksheet. I assume the easiest method of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series.
For
example I have a pie chart with the top 10 franchises (there are
over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I
have
done
this by creating a separate table under the pivot table that
references
the
top 10 using an index lookup function. That works fine but I'd
rather
the
chart were a pivot table so that users can manipulate the data on
the
chart
(rather than on the table). I know that I can sort by the top 10 in
the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific
characters
consistent? For example, lets say a franchise is called Taz, how can
I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Re #1 query. You can also open the Pivot Table Wizard and use the back
button to select a different /larger range.

Yes,
I saw that.
This is the code I used
Sub ColorByCategoryWorksheet()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("LOBTotal").Range("A31:A38")
With Worksheets("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rcategory.Interior.ColorIndex
Next
End With
End Sub

"Jon Peltier" wrote:

I named the worksheets Sheet1 and Sheet2. You have to use your own sheet
names.

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


"Graeme at Raptup" wrote in
message ...
Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the range
were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes that
the
chart is embedded in the same sheet as the pattern table, and that it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active
sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a
difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based
on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the
top
ten.
Offhand, without a lot of experimenting, I don't know how to get the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the
source
data. Will my pivot tables automatically incorporate new data? At
the
moment
I have all my data in one worksheet. I assume the easiest method of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series.
For
example I have a pie chart with the top 10 franchises (there are
over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I
have
done
this by creating a separate table under the pivot table that
references
the
top 10 using an index lookup function. That works fine but I'd
rather
the
chart were a pivot table so that users can manipulate the data on
the
chart
(rather than on the table). I know that I can sort by the top 10 in
the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific
characters
consistent? For example, lets say a franchise is called Taz, how can
I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Thanks,
but I have over 10 tables & charts so that is not really convenient

"artemis" wrote:

Re #1 query. You can also open the Pivot Table Wizard and use the back
button to select a different /larger range.

This is exactly the code I used, and it worked no matter which sheet was
active:

Sub ColorByCategoryLabel2()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range

Set rPatterns = Worksheets("Category Label").Range("A1:A4")
With Worksheets("Test").ChartObjects(1).Chart.SeriesCol lection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rcategory.Interior.ColorIndex
Next
End With
End Sub

It looks like the code I posted and like the code you posted. Which line is
highlighted when the program crashes? Which array do you think has the out
of range subscript (worksheets? vCategories? Points? ChartObjects?
SeriesCollection?)?

Is your chart a standalone chart object, or is it embedded in the worksheet
named "LOB Chart top 8"? If it's a standalone chart sheet, use this:

With Charts("LOB Chart top 8").SeriesCollection(1)

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


"Graeme at Raptup" wrote in
message ...
Yes,
I saw that.
This is the code I used
Sub ColorByCategoryWorksheet()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("LOBTotal").Range("A31:A38")
With Worksheets("LOB Chart top
8").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

"Jon Peltier" wrote:

I named the worksheets Sheet1 and Sheet2. You have to use your own sheet
names.

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


"Graeme at Raptup" wrote in
message ...
Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the range
were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes
that
the
chart is embedded in the same sheet as the pattern table, and that
it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With
Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory =
rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active
sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a
difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something
like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table
based
on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the
top
ten.
Offhand, without a lot of experimenting, I don't know how to get
the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote
in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to
put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to
the
source
data. Will my pivot tables automatically incorporate new data? At
the
moment
I have all my data in one worksheet. I assume the easiest method
of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10
series.
For
example I have a pie chart with the top 10 franchises (there are
over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I
have
done
this by creating a separate table under the pivot table that
references
the
top 10 using an index lookup function. That works fine but I'd
rather
the
chart were a pivot table so that users can manipulate the data on
the
chart
(rather than on the table). I know that I can sort by the top 10
in
the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific
characters
consistent? For example, lets say a franchise is called Taz, how
can
I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Hi Jon,
the highlighted line is
With Worksheets("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)

it is a standalone chart in which case I changed it to

With Charts("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)

But now get a run time error (1004) "Unable to get the chart objects
property of the chart class"

"Jon Peltier" wrote:

This is exactly the code I used, and it worked no matter which sheet was
active:

Sub ColorByCategoryLabel2()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range

Set rPatterns = Worksheets("Category Label").Range("A1:A4")
With Worksheets("Test").ChartObjects(1).Chart.SeriesCol lection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rcategory.Interior.ColorIndex
Next
End With
End Sub

It looks like the code I posted and like the code you posted. Which line is
highlighted when the program crashes? Which array do you think has the out
of range subscript (worksheets? vCategories? Points? ChartObjects?
SeriesCollection?)?

Is your chart a standalone chart object, or is it embedded in the worksheet
named "LOB Chart top 8"? If it's a standalone chart sheet, use this:

With Charts("LOB Chart top 8").SeriesCollection(1)

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


"Graeme at Raptup" wrote in
message ...
Yes,
I saw that.
This is the code I used
Sub ColorByCategoryWorksheet()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("LOBTotal").Range("A31:A38")
With Worksheets("LOB Chart top
8").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

"Jon Peltier" wrote:

I named the worksheets Sheet1 and Sheet2. You have to use your own sheet
names.

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


"Graeme at Raptup" wrote in
message ...
Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the range
were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes
that
the
chart is embedded in the same sheet as the pattern table, and that
it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With
Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory =
rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active
sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a
difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something
like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table
based
on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the
top
ten.
Offhand, without a lot of experimenting, I don't know how to get
the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup" wrote
in
message ...
Hi,
I'm using Excel 2002 and have a number of charts that I want to
put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to
the
source
data. Will my pivot tables automatically incorporate new data? At
the
moment
I have all my data in one worksheet. I assume the easiest method
of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10
series.
For
example I have a pie chart with the top 10 franchises (there are
over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%. I
have
done
this by creating a separate table under the pivot table that
references
the
top 10 using an index lookup function. That works fine but I'd
rather
the
chart were a pivot table so that users can manipulate the data on
the
chart
(rather than on the table). I know that I can sort by the top 10
in
the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific
characters
consistent? For example, lets say a franchise is called Taz, how
can
I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Try this:

With Charts("LOB Chart top 8").SeriesCollection(1)

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



"Graeme at Raptup" wrote in
message ...
Hi Jon,
the highlighted line is
With Worksheets("LOB Chart top
8").ChartObjects(1).Chart.SeriesCollection(1)

it is a standalone chart in which case I changed it to

With Charts("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)

But now get a run time error (1004) "Unable to get the chart objects
property of the chart class"

"Jon Peltier" wrote:

This is exactly the code I used, and it worked no matter which sheet was
active:

Sub ColorByCategoryLabel2()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range

Set rPatterns = Worksheets("Category Label").Range("A1:A4")
With Worksheets("Test").ChartObjects(1).Chart.SeriesCol lection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

It looks like the code I posted and like the code you posted. Which line
is
highlighted when the program crashes? Which array do you think has the
out
of range subscript (worksheets? vCategories? Points? ChartObjects?
SeriesCollection?)?

Is your chart a standalone chart object, or is it embedded in the
worksheet
named "LOB Chart top 8"? If it's a standalone chart sheet, use this:

With Charts("LOB Chart top 8").SeriesCollection(1)

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


"Graeme at Raptup" wrote in
message ...
Yes,
I saw that.
This is the code I used
Sub ColorByCategoryWorksheet()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("LOBTotal").Range("A31:A38")
With Worksheets("LOB Chart top
8").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

"Jon Peltier" wrote:

I named the worksheets Sheet1 and Sheet2. You have to use your own
sheet
names.

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


"Graeme at Raptup" wrote in
message ...
Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the
range
were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes
that
the
chart is embedded in the same sheet as the pattern table, and that
it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With
Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory =
rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the
active
sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote
in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a
difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something
like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table
based
on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at
the
top
ten.
Offhand, without a lot of experimenting, I don't know how to get
the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup"
wrote
in
message
...
Hi,
I'm using Excel 2002 and have a number of charts that I want
to
put
together. I have 3 questions relating to pivot tables &
charts.
(1) I wish to use the same file but each month add new data to
the
source
data. Will my pivot tables automatically incorporate new data?
At
the
moment
I have all my data in one worksheet. I assume the easiest
method
of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10
series.
For
example I have a pie chart with the top 10 franchises (there
are
over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%.
I
have
done
this by creating a separate table under the pivot table that
references
the
top 10 using an index lookup function. That works fine but I'd
rather
the
chart were a pivot table so that users can manipulate the data
on
the
chart
(rather than on the table). I know that I can sort by the top
10
in
the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific
characters
consistent? For example, lets say a franchise is called Taz,
how
can
I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!

Thanks Jon,
works fine!

Thanks for sticking with me on this!

"Jon Peltier" wrote:

Try this:

With Charts("LOB Chart top 8").SeriesCollection(1)

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



"Graeme at Raptup" wrote in
message ...
Hi Jon,
the highlighted line is
With Worksheets("LOB Chart top
8").ChartObjects(1).Chart.SeriesCollection(1)

it is a standalone chart in which case I changed it to

With Charts("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)

But now get a run time error (1004) "Unable to get the chart objects
property of the chart class"

"Jon Peltier" wrote:

This is exactly the code I used, and it worked no matter which sheet was
active:

Sub ColorByCategoryLabel2()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range

Set rPatterns = Worksheets("Category Label").Range("A1:A4")
With Worksheets("Test").ChartObjects(1).Chart.SeriesCol lection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

It looks like the code I posted and like the code you posted. Which line
is
highlighted when the program crashes? Which array do you think has the
out
of range subscript (worksheets? vCategories? Points? ChartObjects?
SeriesCollection?)?

Is your chart a standalone chart object, or is it embedded in the
worksheet
named "LOB Chart top 8"? If it's a standalone chart sheet, use this:

With Charts("LOB Chart top 8").SeriesCollection(1)

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


"Graeme at Raptup" wrote in
message ...
Yes,
I saw that.
This is the code I used
Sub ColorByCategoryWorksheet()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("LOBTotal").Range("A31:A38")
With Worksheets("LOB Chart top
8").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

"Jon Peltier" wrote:

I named the worksheets Sheet1 and Sheet2. You have to use your own
sheet
names.

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


"Graeme at Raptup" wrote in
message ...
Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the
range
were
reference cells (i.e. the cell in A31 was a reference to A7).


"Jon Peltier" wrote:

There's no error proofing in that procedure. The procedure assumes
that
the
chart is embedded in the same sheet as the pattern table, and that
it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With
Worksheets("Sheet2").ChartObjects(1).Chart.SeriesC ollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory =
rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the
active
sheet,
but you'll tell us, won't you?? <g

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


"Graeme at Raptup" wrote
in
message ...
Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a
difference.

Not quite sure where I've gone wrong here!?

"Jon Peltier" wrote:

1. Define a dynamic range on the pivot data worksheet, something
like
this:

Insert menu Names Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSh eet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table
based
on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at
the
top
ten.
Offhand, without a lot of experimenting, I don't know how to get
the
Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/200...ategory-label/

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


"Graeme at Raptup"
wrote
in
message
...
Hi,
I'm using Excel 2002 and have a number of charts that I want
to
put
together. I have 3 questions relating to pivot tables &
charts.
(1) I wish to use the same file but each month add new data to
the
source
data. Will my pivot tables automatically incorporate new data?
At
the
moment
I have all my data in one worksheet. I assume the easiest
method
of
doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10
series.
For
example I have a pie chart with the top 10 franchises (there
are
over
30).
I
have one other pie 'slice' that is 'other' that makes up 100%.
I
have
done
this by creating a separate table under the pivot table that
references
the
top 10 using an index lookup function. That works fine but I'd
rather
the
chart were a pivot table so that users can manipulate the data
on
the
chart
(rather than on the table). I know that I can sort by the top
10
in
the
table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific
characters
consistent? For example, lets say a franchise is called Taz,
how
can
I
ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!