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!