Excel Macro code to Auto Update Pivot Table Range

How to Auto Update Pivot Table Range in excel sheet through VBA Macro Code

For using this VBA macro code in excel to Auto Update Pivot Table Range you will have copy below Macro code in Excel Visual Basic Editor
Sub UpdatePivotTableRange()
Dim Data_Sheet As Worksheet
Dim Pivot_Sheet As Worksheet
Dim StartPoint As Range
Dim DataRange As Range
Dim PivotName As String
Dim NewRange As String
Dim LastCol As Long
Dim lastRow As Long
'Set Pivot Table & Source Worksheet
Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3")
Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")
'Enter in Pivot Table Name
PivotName = "PivotTable2"
'Defining Staring Point & Dynamic Range
Set StartPoint = Data_Sheet.Range("A1")
LastCol = StartPoint.End(xlToRight).Column
DownCell = StartPoint.End(xlDown).Row
Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))
NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1)
'Change Pivot Table Data Source Range Address
Pivot_Sheet.PivotTables(PivotName). _
ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)
'Ensure Pivot Table is Refreshed
'Complete Message
MsgBox "Your Pivot Table is now updated."
End Sub

If you are not using Excel tables then you can use this code to update pivot table range.

What is a Macro Code?

In MS Excel, macro code is a programming code which is written in VBA (Visual Basic for Applications) language.

The idea is to use macro code for automating various activities which you perform manually in Excel, otherwise.

For example, you can use a code to print only a particular range of cells just with a single click instead of selecting the range -> File Tab -> Print -> Print Select -> OK Button.

How to use a Macro Code in Excel


  • On the left side in “Project Window”, right click on the name of your workbook and insert a new module.



  • Just paste your code into the module and close it.



  • Now, go to your developer tab and click on the macro button.


  • It will show you a window with a list of the macros you have in your file from where you can run a macro from that list.





Monthly Business Budget

A monthly business budget provides a precise image of expenses and revenues and gave drive to important business decisions. Whether …

Download Now

Cash Flow Forecast

A cash flow forecast or cash flow projection is like a budget, but rather than computing revenues and expenses, it …

Download Now

Income Statement

Download Income Statement Excel Template to monitor your incomes sources The income statement displays information on the economical results of …

Download Now

Small Business Profit and loss Statement

Download Best Profit and Loss Statement in Excel for small business A profit and loss statement for small business —also …

Download Now

Yearly Profit and Loss Statement

The short term prediction is not a necessary part of a basic business plan. So, the yearly Profit and Loss …

Download Now

Profit and Loss Projection

A profit and loss, or P&L, is a projection of how much money you make by selling services or how …

Download Now