EXCEL VBA SERIES – POST – 15 – [ Auto updation of Pivot table with a new source ]

EXCEL VBA SERIES – POST – 15 – [ Auto updation of Pivot table with a new source ]
Spread the love

 

 

EXCEL VBA SERIES – POST – 15 – [ Auto updation of Pivot table with a new source ]:

In this blog post, we will see how to auto update a pivot table using excel vba​​ for a new source,​​ 

In the previous post we saw on how to auto create a pivot table using a table of data , we will use the same pivot table and redirect the source to a table of data in a different sheet.

We need to change the source data from Data-1 to Data-2

Data-1

Month

Dept

Region

Customer

Revenue

Cost Types

Cost

Jan

Admin

East

ABC

5000

Electricity

300

Jan

IT

West

DEF

4000

Plumbing

500

Feb

HR

North

GHI

2000

Furniture

600

Feb

Finance

South

JKL

6000

Electronics

200

Mar

Admin

East

ABC

5000

Concierge

700

Mar

IT

West

DEF

4000

Other Costs

500

Mar

HR

North

GHI

2000

Plumbing

600

Apr

Finance

South

JKL

6000

Furniture

200

May

Finance

South

JKL

7000

Electronics

700

 

Data-2

Month

Dept

Region

Customer

Revenue

Cost Types

Cost

Jun

HR

East

MNO

5000

Electricity

200

Jun

Finance

West

PQR

4000

Plumbing

700

Jul

Admin

East

STV

2000

Furniture

500

Jul

IT

West

UVW

6000

Electronics

600

Aug

Admin

North

XYZ

5000

Concierge

700

Sep

IT

South

MNO

4000

Other Costs

500

Sep

HR

North

PQR

7000

Plumbing

600

Oct

HR

East

STV

6000

Furniture

500

Nov

Finance

West

UVW

7000

Electronics

600

 

Let see how this done with the below code, The​​ comments are given in line with the code to explain them.

Sub createPivot_newsource()

 

'Get the last row of the new source

With ThisWorkbook.Sheets("newPivot")

 ​​​​ lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

 

'Get the last column of the new source

With ThisWorkbook.Sheets("newPivot")

 ​​ ​​ ​​ ​​ ​​ ​​ ​​​​ LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column

End With

 

'Specify the new range

NewRange = "newPivot!R1C1:R" & lastrow & "C" & LastCol

Debug.Print NewRange

 

'Activate the cell that contain the pivot table

ThisWorkbook.Sheets("Pivot-Table").Cells(1, "A").Activate

 ​​ ​​ ​​​​ 

'Get the pivot table name , so you give the reference to it

Set PT = ActiveCell.PivotTable

Debug.Print PT.Name

 

'Change the pivot cache to new source

ThisWorkbook.Sheets("Pivot-Table").PivotTables(PT.Name).ChangePivotCache​​ ​​ _

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange)

 

'refresh the pivot cache

ThisWorkbook.Sheets("Pivot-Table").PivotTables(PT.Name).PivotCache.Refresh

 

End Sub

 

The Pivot table’ source data will be changed from​​ Data-1 to Data-2.​​ 

 

Thanks for viewing the post, please post your comments below,

The exported module is also available under my github page​​ 

https://github.com/rajathithan/excel-vba/

Please Like and Follow us on facebook and twitter

https://www.facebook.com/gadoth/

https://www.twitter.com/GadothDA



Download

Related posts

Leave a Comment