macro to create pivot table from worksheets of different sizes
Posted by Don on March 13, 2000 7:58 AM
I need a macro that creates a pivot table from a report which is run on a monthly basis. My problem is that the worksheet containing the data for the pivot table is a different size each month. How do I make the macro recognize the number of rows in the worksheet so it can tell the pivot table what range of cells to use?
Posted by Jaime on March 13, 2000 8:38 PM
Dim oPiv_range As Range
Set oPiv_range = Range("A1").CurrentRegion
oPiv_range would be the range for the pivot table,
Assuming ur data starts in range("A1")
Posted by Jen on April 12, 2000 1:17 PM
You could base a pivot table on a named range,
(Insert Menu..Name..Define), and
define the Named Range with an offset formula
so it "knows how many rows deep it is."
The OFFSET does this well. Here's an example:
Definition for "pivot_data_range"
=OFFSET(Data!$A$1:$H$1,0,0,COUNTA(Data!$H:$H))
[put this exact formula into the Named
Range definition, but change the sheet name -
here it is Data]
The 'translation' is as follows:
start with cells a1:h1
[Data!$A$1:$H$1,]
dont move any rows or colums
[0,0,]
the 'depth' of the named range is based
on the number of non-blank rows in column H:
[COUNTA(Data!$H:$H)]
The generic definition for offset is:
OFFSET(reference,rows, cols,height,width)
Remember that this definition relies on all the
cells coming into column H having a value! Its
best to choose a column that will always have
values coming into it. This way, whether you have
10 rows coming in or 10,000 rows, the pivot table
is based on the exact number of variable rows.
