Pivot Table vs Coding

[Mantle51]

I'm not very well versed in PTs(I've always opted for macros to mine data), and have come upon an Excel project laden with them. The users in the group aren't excel oriented and have little idea of how to use PTs(they were created by some other dept). Anyway, I'm thinking, using input box functions and perhaps user forms, coding would do the trick and them some. PTs have no unique identification in VBA, correct?

Any thoughts on melding the two?

THanks

 

[SkipVought]

Hi,

Check out the Excel Object Model.

PT's are an object on a Worksheet. Each Worksheet has a Collection of zero or more PivotTables. So you can, for instance, do this...

dim ws as worksheet, pt as pivottable
for each ws in worksheets
  for each pt in ws.pivottables
    pt.pivotcache.refresh
  next
next

and it will execute without error even when there are no pt's

BTW, I'd use them and get the users to understand them. They are a VERY powerful tool.

Skip,

_[red][/red]

 

[xwb]

I've never used PTs myself but I have seen them in use and as said before, they are a very powerful tool. You need to go through the tutorials a few times to figure out

1) how to use them
2) what is required to use them
3) when to use them

They may not be suitable for all applications. Initially it is difficult not to be a child with a hammer where everything looks like a nail. I've seen some applications which have been bent to use PTs when they are totally unnecessary: hence point 3.

 

[xlbo]

I certainly wouldn't bother coding a bunch of VBA if pivot tables answer the question - I have always been of the opinion that if you can do something (pretty easily) with native functionality, you should do that before looking to code.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question