Blair246810
10-26-2016, 08:01 AM
My goal is either to get this macro to work or find another macro that will do the same thing. This macro was designed to
connect existing Pivot Tables to a new data source.
As I said, this macro did work at one time. I am not sure when it stopped working. I have run this macro on other computers and it produces the same error.
The exact message is "Runtime error 5 Invalid Procedure Call or Argument".
My version of Microsoft Office is 2010 and the operating system is Windows 7 Professional.
I have attempted to attach a sample file; however, the file would not upload properly.
The following is the code:
Sub AdjustAllPivotDataRanges()
'PURPOSE: Dynamically change every pivot table's data source range in the worksheet
'SOURCE: TheSpreadsheetGuru
Dim sht As Worksheet
Dim pvt As PivotTable
Dim StartPoint As Range
Dim rng As Range
Dim SourceAddress As String
Dim ws As Worksheet
'Enter Worksheet Name that holds your Pivot data source
Set sht = ActiveWorkbook.Worksheets("PivotData")
'Enter first cell in your Pivot data source
Set StartPoint = sht.Range("A1")
'Create SourceData address
'Set NewRange = sht.Range("PivotDataUpdate")
Set rng = sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
SourceAddress = sht.Name & "!" & rng.Address(ReferenceStyle:=xlR1C1)
'Get to current Pivot Tables to update new source data
'Worksheets(Worksheets.Count).Select
'For Each sht In ThisWorkbook.Worksheets
Set ws = ActiveWorkbook.ActiveSheet
' Worksheets(Worksheets.Count.Select
'Loop through and update pivot tables with new data source range
For Each pvt In ws.PivotTables
' Change Pivot Table's data source range address
'Application.DisplayAlerts = False
pvt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceAddress)
' pvt.ChangePivotCache _
' ThisWorkbook.PivotCaches.Create( _
' SourceType:=xlDatabase, _
'SourceData:=SourceAddress)
' Application.DisplayAlerts = True
'Ensure Pivot Table is refreshed
pvt.RefreshTable
Next pvt
'Completion Message
MsgBox "All Pivot Table Data Source Ranges have been updated in this worksheet!", vbInformation
End Sub
connect existing Pivot Tables to a new data source.
As I said, this macro did work at one time. I am not sure when it stopped working. I have run this macro on other computers and it produces the same error.
The exact message is "Runtime error 5 Invalid Procedure Call or Argument".
My version of Microsoft Office is 2010 and the operating system is Windows 7 Professional.
I have attempted to attach a sample file; however, the file would not upload properly.
The following is the code:
Sub AdjustAllPivotDataRanges()
'PURPOSE: Dynamically change every pivot table's data source range in the worksheet
'SOURCE: TheSpreadsheetGuru
Dim sht As Worksheet
Dim pvt As PivotTable
Dim StartPoint As Range
Dim rng As Range
Dim SourceAddress As String
Dim ws As Worksheet
'Enter Worksheet Name that holds your Pivot data source
Set sht = ActiveWorkbook.Worksheets("PivotData")
'Enter first cell in your Pivot data source
Set StartPoint = sht.Range("A1")
'Create SourceData address
'Set NewRange = sht.Range("PivotDataUpdate")
Set rng = sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
SourceAddress = sht.Name & "!" & rng.Address(ReferenceStyle:=xlR1C1)
'Get to current Pivot Tables to update new source data
'Worksheets(Worksheets.Count).Select
'For Each sht In ThisWorkbook.Worksheets
Set ws = ActiveWorkbook.ActiveSheet
' Worksheets(Worksheets.Count.Select
'Loop through and update pivot tables with new data source range
For Each pvt In ws.PivotTables
' Change Pivot Table's data source range address
'Application.DisplayAlerts = False
pvt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceAddress)
' pvt.ChangePivotCache _
' ThisWorkbook.PivotCaches.Create( _
' SourceType:=xlDatabase, _
'SourceData:=SourceAddress)
' Application.DisplayAlerts = True
'Ensure Pivot Table is refreshed
pvt.RefreshTable
Next pvt
'Completion Message
MsgBox "All Pivot Table Data Source Ranges have been updated in this worksheet!", vbInformation
End Sub