Get Pivot Table count of rows in VBA
 

Hello again,

Each answer just clears me long enough to find the next
question. But I'm almost home.

I have a macro which creates 3 pivot tables on a single
tab. Essentially the first one shows "active" stuff, the
2nd shows "potential" and the third shows "completed"
items. (that's the way management wants to see them).

The trick is that right now, I have it hard-coded to
create one at row 15, the 2nd at row 100 and the third at
row 200, because I can't predict how big they'll be.

Is there a way to ask Excel to go to the end of a pivot
table, move down 2 rows, and then start the next one??
Or just get a count of the pivot size, so i can
programmatically do the same thing.

As always, any help is sincerely appreciated.

Thanks,
Randy

Get Pivot Table count of rows in VBA
 

Assuming your tables start in column A and there is an entry in A on the
last row, the following will return the cell two cells below that entry:

dim cell as range
set cell = cells(65536,1).end(xlup).offset(2,0)

You can then use

TableDestination:=cell

in the pivottablewizard statement to specify the destination.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Randy" wrote in message
...
Hello again,

Each answer just clears me long enough to find the next
question. But I'm almost home.

I have a macro which creates 3 pivot tables on a single
tab. Essentially the first one shows "active" stuff, the
2nd shows "potential" and the third shows "completed"
items. (that's the way management wants to see them).

The trick is that right now, I have it hard-coded to
create one at row 15, the 2nd at row 100 and the third at
row 200, because I can't predict how big they'll be.

Is there a way to ask Excel to go to the end of a pivot
table, move down 2 rows, and then start the next one??
Or just get a count of the pivot size, so i can
programmatically do the same thing.

As always, any help is sincerely appreciated.

Thanks,
Randy

Get Pivot Table count of rows in VBA
 

set rng = cells(rows.count,1).End(xlup)

will give you the last filled cell in column 1 (column A). You can then use
that as a reference to have your code place the next pivot table.

also, if you look in the object browser under the pivottable object, there
are several range properties which define the extent of the pivot table
(after it is created).

--
Regards,
Tom Ogilvy



"Randy" wrote in message
...
Hello again,

Each answer just clears me long enough to find the next
question. But I'm almost home.

I have a macro which creates 3 pivot tables on a single
tab. Essentially the first one shows "active" stuff, the
2nd shows "potential" and the third shows "completed"
items. (that's the way management wants to see them).

The trick is that right now, I have it hard-coded to
create one at row 15, the 2nd at row 100 and the third at
row 200, because I can't predict how big they'll be.

Is there a way to ask Excel to go to the end of a pivot
table, move down 2 rows, and then start the next one??
Or just get a count of the pivot size, so i can
programmatically do the same thing.

As always, any help is sincerely appreciated.

Thanks,
Randy

Get Pivot Table count of rows in VBA
 

Randy,

After you've placed the first pivot table in row 15 (let's just say column B
for grins) then

Dim myRow As Long
myRow = Worksheets("Pivot Table Sheet
Name").Range("B15").CurrentRegion.Rows.Count + 15

will be the first blank row below your table.

To make it a littl emore flexible:
Dim myRow As Long
Dim myCell As Range

Set myCell = Worksheets("Pivot Table Sheet Name").Range("B15")
myRow = myCell.CurrentRegion.Rows.Count + myCell.Row

So you could place your next table at

Worksheets("Pivot Table Sheet Name").Range("B" & myRow + 3)

to give yourself three extra blank rows between tables.

HTH,
Bernie
MS Excel MVP

"Randy" wrote in message
...
Hello again,

Each answer just clears me long enough to find the next
question. But I'm almost home.

I have a macro which creates 3 pivot tables on a single
tab. Essentially the first one shows "active" stuff, the
2nd shows "potential" and the third shows "completed"
items. (that's the way management wants to see them).

The trick is that right now, I have it hard-coded to
create one at row 15, the 2nd at row 100 and the third at
row 200, because I can't predict how big they'll be.

Is there a way to ask Excel to go to the end of a pivot
table, move down 2 rows, and then start the next one??
Or just get a count of the pivot size, so i can
programmatically do the same thing.

As always, any help is sincerely appreciated.

Thanks,
Randy

Get Pivot Table count of rows in VBA
 

Bob,

You can get burned using End(xlUp) with pivot tables, if there are no totals
and there are multiple row fields.

HTH,
Bernie
MS Excel MVP

"Bob Flanagan" wrote in message
...
Assuming your tables start in column A and there is an entry in A on the
last row, the following will return the cell two cells below that entry:

dim cell as range
set cell = cells(65536,1).end(xlup).offset(2,0)

You can then use

TableDestination:=cell

in the pivottablewizard statement to specify the destination.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Randy" wrote in message
...
Hello again,

Each answer just clears me long enough to find the next
question. But I'm almost home.

I have a macro which creates 3 pivot tables on a single
tab. Essentially the first one shows "active" stuff, the
2nd shows "potential" and the third shows "completed"
items. (that's the way management wants to see them).

The trick is that right now, I have it hard-coded to
create one at row 15, the 2nd at row 100 and the third at
row 200, because I can't predict how big they'll be.

Is there a way to ask Excel to go to the end of a pivot
table, move down 2 rows, and then start the next one??
Or just get a count of the pivot size, so i can
programmatically do the same thing.

As always, any help is sincerely appreciated.

Thanks,
Randy

Get Pivot Table count of rows in VBA
 

Great. Thanks all!

I appreciate all the options. :)

thx,
Randy

-----Original Message-----
Randy,

After you've placed the first pivot table in row 15
(let's just say column B
for grins) then

Dim myRow As Long
myRow = Worksheets("Pivot Table Sheet
Name").Range("B15").CurrentRegion.Rows.Count + 15

will be the first blank row below your table.

To make it a littl emore flexible:
Dim myRow As Long
Dim myCell As Range

Set myCell = Worksheets("Pivot Table Sheet Name").Range
("B15")
myRow = myCell.CurrentRegion.Rows.Count + myCell.Row

So you could place your next table at

Worksheets("Pivot Table Sheet Name").Range("B" & myRow +
3)

to give yourself three extra blank rows between tables.

HTH,
Bernie
MS Excel MVP

"Randy" wrote in message
...
Hello again,

Each answer just clears me long enough to find the next
question. But I'm almost home.

I have a macro which creates 3 pivot tables on a single
tab. Essentially the first one shows "active" stuff,
the
2nd shows "potential" and the third shows "completed"
items. (that's the way management wants to see them).

The trick is that right now, I have it hard-coded to
create one at row 15, the 2nd at row 100 and the third
at
row 200, because I can't predict how big they'll be.

Is there a way to ask Excel to go to the end of a pivot
table, move down 2 rows, and then start the next one??
Or just get a count of the pivot size, so i can
programmatically do the same thing.

As always, any help is sincerely appreciated.

Thanks,
Randy


.