Excel Macro code to Auto Update Pivot Table Range

Share on facebook
Share on linkedin
Share on email
Share on whatsapp

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
Data_Sheet.Activate
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
Pivot_Sheet.PivotTables(PivotName).RefreshTable
'Complete Message
Pivot_Sheet.Activate
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

click-on-visual-basic-editor-before-you-use-these-useful-macros-for-excel

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

add-module-to-paste-these-useful-macros-for-excel

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

use-useful-macro-codes-examples-by-pasting-them-into-vb-editor

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

useful-macro-codes-examples-to-use-from-macro-options

  • 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.

useful-macro-codes-examples-list-from-macro-option

 

RECENTLY UPLOADED EXCEL TEMPLATES

Training_Attendance_Sheet_V1.0

Training Attendance Sheet

Whether you’re organizing any training program for an athletic event. A technology course, or a chess tournament, a good attendance …

Download Now
Weekly_Attendance_Sheet_V1.0

Weekly Attendance Sheet

Our “Weekly Attendance Sheet” is simple, compact, and useful an all-purpose attendance tracker. This attendance tracker includes one worksheet with …

Download Now
Student_Attendance_Sheet_Weekly_Monthly

Student Attendance Sheet

These student attendance sheet records is create to help teachers keep track of attendance of their students. There are two …

Download Now
Shift_Planner_with_Attendance_Tracker_Pro-employee

Shift Planner with Tracker

Shift Planner with Tracker for Excel is a powerful spreadsheet that help any Human Resources or operational managers creating their …

Download Now
Employee_Attendance_Tracker_Pro_V2.55

Employee Attendance Tracker

The free employee attendance tracker template is one of my popular excel templates. I have create a more general employee …

Download Now
Islamic_Calendar

Islamic Calendar

The Islamic calendar (it is known also as Muslim calendar or Hijri calendar) is a lunar calendar based on 12 …

Download Now