Locating Pivot Table in VBA

  • Thread starter Thread starter Jack Gillis
  • Start date Start date
J

Jack Gillis

I have a macro created in VBA that creates two Pivot Tables, one under
the other. I works fine except I run into problems when the first table
shows up with more rows than I expected when, in the VBA code, I set
the table destination of the second table. Then the macro doesn't do
well at all.

I there a way I can set the table destination of the second table
relative to the bottom of the first table?

A statement something like this:

(preceding stuff omitted).CreatePivotTable
TableDestination:="[Workbookname.xls]SheetName!'Three rows below the
table above" and so forth.

I will appreciate any help on this.

Thank you very much.
 
You could count the rows in the first pivot table, and add a few rows
for space between the two tables:

Dim ws As Worksheet
Dim pt As PivotTable
Dim rngPT As Range
Dim lPT2Row As Long
Dim iSpace As Integer
Dim strPTName As String
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set rngPT = pt.TableRange2
iSpace = 5
strPTName = "PT2"

lPT2Row = rngPT.Rows(rngPT.Rows.Count).Row + iSpace

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Data!PivotRange").CreatePivotTable _
TableDestination:="Pivot!" & _
ws.Cells(lPT2Row, 1).Address(ReferenceStyle:=xlR1C1), _
TableName:=strPTName


Jack said:
I have a macro created in VBA that creates two Pivot Tables, one under
the other. I works fine except I run into problems when the first table
shows up with more rows than I expected when, in the VBA code, I set
the table destination of the second table. Then the macro doesn't do
well at all.

I there a way I can set the table destination of the second table
relative to the bottom of the first table?

A statement something like this:

(preceding stuff omitted).CreatePivotTable
TableDestination:="[Workbookname.xls]SheetName!'Three rows below the
table above" and so forth.

I will appreciate any help on this.

Thank you very much.
Click to expand...
 
Thank you very much Debra

I will give that a try later today. It seems straightforward.

Jack


Debra Dalgleish said:
You could count the rows in the first pivot table, and add a few rows
for space between the two tables:

Dim ws As Worksheet
Dim pt As PivotTable
Dim rngPT As Range
Dim lPT2Row As Long
Dim iSpace As Integer
Dim strPTName As String
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
Set rngPT = pt.TableRange2
iSpace = 5
strPTName = "PT2"

lPT2Row = rngPT.Rows(rngPT.Rows.Count).Row + iSpace

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Data!PivotRange").CreatePivotTable _
TableDestination:="Pivot!" & _
ws.Cells(lPT2Row, 1).Address(ReferenceStyle:=xlR1C1), _
TableName:=strPTName


Jack said:
I have a macro created in VBA that creates two Pivot Tables, one
under the other. I works fine except I run into problems when the
first table shows up with more rows than I expected when, in the VBA
code, I set the table destination of the second table. Then the
macro doesn't do well at all.

I there a way I can set the table destination of the second table
relative to the bottom of the first table?

A statement something like this:

(preceding stuff omitted).CreatePivotTable
TableDestination:="[Workbookname.xls]SheetName!'Three rows below the
table above" and so forth.

I will appreciate any help on this.

Thank you very much.
Click to expand...
Click to expand...
 
Back
Top