Excel Function to return Pivot Table Display Range in WorkSheet
***Excel Function to return Pivot Table Display Range in WorkSheet*** // 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 End Function