Solved

dynamically change a Pivot Table with VBA

Posted on 2014-11-10
2
330 Views
Last Modified: 2014-11-12
Very often our data will change from sat 100 items to 700 to 300 to 1000. This gives us a problem with the Pivots.

I found the below code on the web to dynamically change a pivot table but it does not work as it throws out an error saying that "one of the data columns has a blank heading" but in fact non of them are blank.

Is it possible an Expert out there could check out the code for me please, or provide me with VBA code that would do what I am looking to do.

I did try to contact the owner but I am unable to do so.

Sub AdjustPivotDataRange()
'PURPOSE: Automatically readjust a Pivot Table's data source range
'SOURCE: www.TheSpreadsheetGuru.com/The-Code-Vault

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("Sheet1")
  Set Pivot_sht = ThisWorkbook.Worksheets("Sheet2")

'Enter in Pivot Table Name
  PivotName = "PivotTable1"

'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 re-run!.", vbCritical, "Column Heading Missing!"
    Exit Sub
  End If

'Change Pivot Table Data Source Range Address
  Pivot_sht.PivotTables(PivotName).ChangePivotCache _
    ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=NewRange)
     
'Ensure Pivot Table is Refreshed
  Pivot_sht.PivotTables(PivotName).RefreshTable

'Complete Message
  MsgBox PivotName & "'s data source range has been successfully updated!"

End Sub


Thanks
0
Comment
Question by:Jagwarman
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40432214
Instead of code, I suggest you use a dynamic data source - either a defined name or a Table. Debra's pafge here has very clear instructions: http://www.contextures.com/xlPivot01.html
If you have any issues with that, please let us know.
0
 

Author Comment

by:Jagwarman
ID: 40432249
Thanks Rory that works for me. Not sure why but I cannot close questions from my pc I will do it from home later
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question