When using Linked Excel Object in Word Report , Word uses absolute address to link object in Excel.
This way could cause error when Excel Object Range is changed. For example, Pivot Table X-Y size changed, the linked range in Word will become incorrect.
So the workaround is using Named Range in Excel. Unfortunately, there is direct function in Excel to do that.
Using Offset & counta function is one way we can make it. However, when user put several Pivot Table in same sheet, counta might not be correct value.
So I create a Excel function to handle this situation. Any suggestion is welcome.
Function PVRange(Sheet_Name, Pivot_Name) As Range
' function to return PivotTable Display Range
Dim pvt As PivotTable
Dim PVRange As Range
Set pvt = Worksheets(Sheet_Name).PivotTables(Pivot_Name)
Set PVRange = pvt.TableRange1