Adjust The Data Range In Pivot Table(EXCEL)
Sub AdjustPivotDataRange() Dim Data_sht As Worksheet Dim Pivot_sht As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String 'Set Variables Equal to Data Sheet and Pivot Sheet Set Data_sht = ThisWorkbook.Worksheets("Data_Sheet_name") Set Pivot_sht = ThisWorkbook.Worksheets("Pivot_Sheet_name") 'Enter in Pivot Table Name PivotName = "National" 'Dynamically Retrieve Range Address of Data Set StartPoint = Data_sht.Range("A1") Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell)) NewRange = Data_sht.Name & "!" & _ DataRange.Address(ReferenceStyle:=xlR1C1) 'Make sure every column in data set has a heading and is not blank (error prevention) If WorksheetFunction.CountBlank(DataRange.Rows(1)) > 0 Then MsgBox "One of your data columns has a blank heading." & vbNewLine _ & "Please fix and r