white_rhino wrote in message ...
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,

Why don't you use a greater database ? For instance ("R1000"). The
empty rows you can hide them in the pivot table. Is this maybey a
solution for you ?

Robert