white_rhino wrote:
hi all,

i'm in the process of making a macro that will automatically create a
pivot table for me each month. the problem is that the data i work with
each month changes; that is, number of rows of data used for the pivot
table is not always the same. for example, this month i could have 754
rows and 766 rows next month.

i tried using a variable instead of an exact cell number, but excel
seems to be really picky and wants an specific number instead. is it
possible to create a macro for making pivot tables, even if the number
of rows varies on a monthly basis?

here are a few lines from the macro, if it helps:

Range("K2").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"BFCCL!R1C1:R754C9").CreatePivotTable
TableDestination:=Range("K2"), _
TableName:="PivotTable2"

- the worksheet used ("BFCCL"), the starting cell ("R1C1") and the last
column number ("C9") all remain the same per month.
- only the row number ("R754") changes.

i was wondering if anybody out there could help me out with this
problem, as i'm totally stumped. i'd really, really appreciate it.
thanks in advance,

-fred


Fred,
The best way to deal with this is to base the pivot table on a named
range. Each month you can resize the named range to accommodate the
extra rows. The size of the range can be changed using VBA or a better
way is to use a dynamic named range. To do this use the offset function
in the Refers to: box in the Define Name dialogue box. The following
formula creates a named range that has A3 as the top left cell. It uses
the COUNTA() function to count the number of non-empty cells in column A
and the number of non-empty cells in row 3. It therefore automatically
adjusts the range size to the number of rows and columns in the data.

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$6553 6),COUNTA(Sheet1!$A$3:$IU$3))

All you need to do each month is:

Delete the existing data from the named range.
Copy in the new data.
Refresh the pivot table. The pivot table will automatically pick-up the
new data range.
The above 3 steps can be done using a few lines of VBA.

If you have a large data set, once the pivot table has been refreshed
you can delete the data from the data range (assuming that you've
checked the Save data with table layout option the PivotTable Options
dialogue. The pivot table only refers to the original data range when
you issue the refresh command.

If you have other pivot tables that will use the same data, base them on
the first pivot table and they will be updated at the same time. This
will reduce the size of your file.

Good luck

cheers
peterDavey
Melbourne
Australia