dynamic range and code 1

[Chrissirhc]

Hi,

When I run this code:

Sub refreshExclusionPivot()

Dim myPivotTable As PivotTable
Dim myPivotOutline As Range
Dim myPivotInternalFormatRange As Range

Set myPivotOutline = Range("Pivot_outline")
Set myPivotInternalFormatRange = Range("Pivot")

Set myPivotTable = Worksheets("valuations").PivotTables("Exclusions_pivot")

myPivotOutline.Borders.LineStyle = xlNone

Application.DisplayAlerts = False

myPivotTable.PivotCache.Refresh

'Format pivot table
myPivotOutline.Borders(xlInsideHorizontal).LineStyle = xlNone

With myPivotInternalFormatRange.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

With myPivotInternalFormatRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myPivotInternalFormatRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myPivotInternalFormatRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myPivotInternalFormatRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With myPivotInternalFormatRange
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With myPivotInternalFormatRange.Font
.Name = "Arial"
.SIZE = 10
.Bold = False
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 48
End With

myPivotOutline.Calculate

' Pivot table outline formatting
With myPivotOutline.Cells.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myPivotOutline.Cells.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myPivotOutline.Cells.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With myPivotOutline.Cells.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Application.DisplayAlerts = True

End Sub

My ranges to do not get formatted correctly.

myPivotOutline refers to [Pivot_Outline] which is a dynamic range "=OFFSET(Sheet1!$I$2,0,0,MATCH("*",Sheet1!$I:$I,-1)-1,1)"

The problem that occurs is that myPivotOutline borders are not correctly set (they turn out to border where the range originally extended to) even though the size of the range has changed. Is there any way to force myPivotRange to have the correct address in code? myPivotOutline.calculate didn't seem to help.

Thanks,

Chris

 

[PHV]

Execute the following:
myPivotTable.PivotCache.Refresh
BEFORE instantiating your range objects.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Chrissirhc]

It does thanks, but I have no idea how you worked that out?