Pivot Tables - Multiple Consolidation Ranges

C

Chuck Harkes

Hi all. I use Pivot tables quite frequently to do consolidations.
Since I frequently consolidate up to 100 or more workbooks at a time
it's kind of a pain to enter all of the ranges separately in step 2b
of the Pivot Table Wizard. I'm thinking there must be a way to maybe
use VBA to: 1) Enter the ranges to consolidate without me having to
manually enter. 2) Construct the Pivot Table. I'm a novice at VBA
and I did use the recorder to see if I could figure the necessary code
on my own but I'm at a standstill.

Can anyone point me in the right direction?

Many Thanks
Andrew
 
R

Ron de Bruin

Hi Chuck

I don't work with Pivot tables frequently so I don't have
a answer for you at the moment.

You can find information on this site's
I hope you find what you are looking for

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
 
T

Tom Ogilvy

Turn on the macro recorder and set up your pivot table with a small number
of workbooks.

then modify the recorded code to do what you want.
 
D

dkenebre

Do you remember your response to the Multiple conditions and formulas
post a few days ago, well I've updated the response because for
clarity, because I am still working on the problem, can you take
another took at the problem:

Let me explain this further what I would like to do:
I would like to take the numbers in cell A1, B1 and C1 and
compare it to numbers in D1, column D is for H numbers
and then compare it to E1, column E is for T numbers
and then compare it to F1, column F is for C numbers

1. If the 3 cells have all 3 numbers in D1, then G1:I1=HHH
and J1=HHH, because of the order it occurred in.

2. If the 3 cells have one cell number in D1, and one in E1, and one in
F1, then G1:I1=HTC
and J1 could equal THC or TCH or CHT or CTH or HCT or HTC, depending on
the order it occurred in.

3. If the 3 cells have two cells have numbers in D1, and one in E1, and
none in F1, then G1:I1=HHT
and J1=THC, depending on the order it occurred in.

4. If the cells have one cells that has a number in D1, two in E1, and
none in F1, then G1:I1=HTT
and J1 could equal HTT, THT,TTH, depending on the order.

5. If there were one in D1, none in E1 and two in F1, then G1:I1=HCC
and J1 could be HCC, CHC, or CCH

6. If none in D1, two in E1, and one in F1, then G1:I1=TTC
and J1 could be TTC, TCT or CTT

7. If none in D1, all 3 in E1 and none in F1, then G1:I1=TTT
and J1 would be TTT.

8. If none in D1, 1 in E1, 2 in F1, then G1:I1=TCC
and J1 could be TCC, CTC, or CCT

9. IF none in D1 and none if E1 and all 3 in F1, then G1:I1=CCC
and J1 would be CCC

10. if 2 in D1, and none in E1 and 1 in F1, then G1:I1=HHC
and J1 could be HHC, HCH, CHH


sample #1
If Value =372
H=193
T=0248
C=675
Then value 372 = HTC and HCT

Sample#2
Value=203
H=193
T=0248
C=675
Then value 372 = HTT and HTT

Chart would look something like this:
using sample #1
,,,,,,,,,,,,H num,,,T num,,,C num
A1 B1 C1,,,,,D1,,,,,,E1,,,,,,F1,,,,G1,H1,I1,,,J1
3,,,7,,,2,,,,,193,,,,0248,,,675,,,,H,,,T,,,C,,,HCT


,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,bl,,Grn,,R



and so on........
So, I would need a formula for G1,H1,I1 and J1.

Also, I would like to automatically highlight background to be
different colors in the G, H and I columns for each letter code, for
example:
Hs= blue background
Ts= Green
Cs= Red
just for the G, H and I columns, the J column will all be the same
color
and so on......
 
R

Ruud Hurkmans

modify to total colomn.

Regards
Ruud

Tom Ogilvy wrote in message said:
Turn on the macro recorder and set up your pivot table with a small number
of workbooks.

then modify the recorded code to do what you want.
Click to expand...
 
Top