I am cleaning Excel worksheets for input into another program (Recorder 6). I
have found a routine involving pivot tables which does the best job, however
I need to run the wizard for each new sheet. Is there a way of saving the
pivot table layout & then simply run it for each new workbook?

There are usually 100 columns of up to 10 rows (after a transition) involved
here, each of which apparently needs to be manually edited (from 'count of'
to 'Sum of' ) in order to work.

Cheers now, Rob

It's not clear what you're doing, but perhaps you could turn on the
macro recorder while you run the wizard on one worksheet, and make the
changes.
Then, run the recorded macro on a copy of another sheet, to see if it
works the way you want.

Rob B wrote:
I am cleaning Excel worksheets for input into another program (Recorder 6). I
have found a routine involving pivot tables which does the best job, however
I need to run the wizard for each new sheet. Is there a way of saving the
pivot table layout & then simply run it for each new workbook?

There are usually 100 columns of up to 10 rows (after a transition) involved
here, each of which apparently needs to be manually edited (from 'count of'
to 'Sum of' ) in order to work.

Cheers now, Rob


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi ,

sorry, but I've no idea how to use macros- I tried & nothing happened.

The source data looks like this:
COMPARTMENT 1 2 3 4 5
B-headed Gull
Black Grouse
Blackbird
Blackcap
Blue Tit 8 4
Brambling 3
Bullfinch

With various headers etc. I need the cleaned version to look like this:

Species Date Location Abundance Weather/Comment
B-headed Gull
Black Grouse 24/10/2002 LOA-Comp 7 1 Sunny spells and showers after
overnight frost
Blackbird 30/10/2002 LOA-Comp 8 2 WEATHER - misty, hazy sun trying to break
through, very light northerly wind
Blackbird 12/11/2002 LOA-Comp 3 1


with each row containing all the data.

Thoughts & hlp would be great as I've got around 8000 records to process &
am fed up making a pivot table over & over agian for each sheet- which are
usually 'slightly' different contents.

Cheers now, Rob.

Hi Rob

Could you define your data source for the PT as Dynamic Range. For more
help on this take a look at Debra's site
http://www.contextures.com/xlNames01.html#Dynamic

Then in the PT wizard, use the named range =myData1 as the source
rather than something like $A$1:$H$1000.

If you make different ranges myData1, myData2 etc. then just changing
the source range in the PT will give your results.

--
Regards

Roger Govier


"Rob B" wrote in message
...
Hi ,

sorry, but I've no idea how to use macros- I tried & nothing happened.

The source data looks like this:
COMPARTMENT 1 2 3 4 5
B-headed Gull
Black Grouse
Blackbird
Blackcap
Blue Tit 8 4
Brambling 3
Bullfinch

With various headers etc. I need the cleaned version to look like
this:

Species Date Location Abundance Weather/Comment
B-headed Gull
Black Grouse 24/10/2002 LOA-Comp 7 1 Sunny spells and showers after
overnight frost
Blackbird 30/10/2002 LOA-Comp 8 2 WEATHER - misty, hazy sun trying to
break
through, very light northerly wind
Blackbird 12/11/2002 LOA-Comp 3 1


with each row containing all the data.

Thoughts & hlp would be great as I've got around 8000 records to
process &
am fed up making a pivot table over & over agian for each sheet- which
are
usually 'slightly' different contents.

Cheers now, Rob.

Ok what I've done is simply go 2 steps back via the wizard to a new set of
data- usually a transposed sheet of original data.

Sometimes I need to add new flelds to the data, sometimes not.

I use Deborah's fantastic pivot table addin to reset all data fields to 'Sum
of' & procede from there.

No code, just a few easy , repetitive steps.
Cheers now, Rob.