Pivot tables - position on sheet

[ajm]

hi all, am needing to add a second pivot table to each of my existing reports and want it t be offset from the first so that any changes in the first will move the second also. essentially, the second pivot will be in column C starting 5 rows beneath the first pivot table. can't seem top find any posts on the topic??

also, why does my formula:

=OFFSET(Import!$a$1,0,0,COUNTA(Import!B:B),COUNTA(Import!1:1))

not produce the named range it should?

 

[SydneyGeek]

The few times I've tried that, it has either been with a defined and unchanging number of row items or by allowing enough rows for the top PT to expand without crashing into adjacent tables. I don't know of a way to make a PT gracefully move a second PT, so my approach would be to leave, say, 150 blank rows between the tables and hide the empty rows to give the correct visual spacing.

EDIT -- Second question -- Are there any gaps in column B? Do you get a range that's a few rows short, or don't you get a range at all?

Denis

 

[Yard]

Agree with Denis - easier to allow for expansion than attempt to capture it. That said, I was just playing around with this. Not fully tested, and I'm sure there are better methods, but this will insert a blank column and / or row around the Pivot Table if it finds values it its way.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ws As Worksheet
Dim rngCornerBR, rngCornerTR, rngCornerBL As Range
 
With Target
   Set ws = .Parent
   Set rngCornerBR = ws.Cells(.TableRange1.Rows.Count + .TableRange1.Row, .TableRange1.Columns.Count + .TableRange1.Column)
   Set rngCornerTR = ws.Cells(.TableRange1.Row, .TableRange1.Columns.Count + .TableRange1.Column)
   Set rngCornerBL = ws.Cells(.TableRange1.Rows.Count + .TableRange1.Row, .TableRange1.Column)
End With
 
 
On Error Resume Next
 
If WorksheetFunction.CountA(Range(rngCornerTR, rngCornerBR)) > 0 Then rngCornerBR.EntireColumn.Insert
If Err = 1004 Then
    MsgBox "Could not insert a column as there is another pivot table spanning column " & rngCornerBR.Column, _
    vbOKOnly + vbInformation, "Trying to make space around " & Target.Name
End If
 
If WorksheetFunction.CountA(Range(rngCornerBL, rngCornerBR)) > 0 Then rngCornerBR.EntireRow.Insert
If Err = 1004 Then
    MsgBox "Could not insert a row as there is another pivot table spanning row " & rngCornerBR.Row, _
    vbOKOnly + vbInformation, "Trying to make space around " & Target.Name
 
End If
 
End Sub

 

[ajm]

thanks guys. will try pivot stuff this morning, kids allowing. and denis, no range shows up at all. nitind showed me a method of resizing an existing named range a month or two ago which i will use in the meantime. however, i would loive to know why this doesn't work as it really should. no blank cells in the count range in either direction. ??? anyway, i was thinking i might be a way of naming the range that contains the pivot and then i could offset from the bottom row of that.

 

[SydneyGeek]

Just looked again. The range reference should be absolute, or else it moves around and you will get nothing. Change to:

=OFFSET(Import!$a$1,0,0,COUNTA(Import!$B:$B),COUNTA(Import!$1:$1))

Denis