qitjch
02-12-2016, 02:52 PM
I am using the following code to update the source data of 3 different pivot tables. The code was working fine all week and then today for some reason starting spitting a run-time error at me saying, "The PivotTable field name is not valid." I have no idea what could be wrong. I've triple checked the pivot table names, as well as the source data to ensure it was selecting the proper range. All of my columns have headers, etc.
Code:
'updates source data and refreshes pivot tables
Sub Pivot_refresh()
'declare variables
Dim pt As PivotTable
Dim ws As Worksheet
Dim mycells As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''
' CC summary Pivot Table Update'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
'Selects all of the data on ppv combine tab.
Worksheets("PPV Combine").Activate
ActiveSheet.Range("A1").Select
Selection.CurrentRegion.Select
'resizes selection to only include first 4 columns and assigns to variable mycells.
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
Set mycells = Selection
MsgBox (mycells.Address)
'update source data and refresh cc summary pivot
Sheets("CC summary").Select
Range("B5").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address), _
Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Oracle-Pivot Table Update'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Selects current region on ppv-oracle tab
Worksheets("PPV-Oracle").Activate
ActiveSheet.Range("A2").Select
Selection.CurrentRegion.Select
'resizes the selection to exclude the top row and last column and assigns to variable mycells.
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
Set mycells = Selection
'update source data and refresh oracle -pivot
Worksheets("Oracle -Pivot").Select
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV-Oracle!" & mycells.Address _
, Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
' PPV by Vendory Pivot Table Update'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
'Selects all of the data on ppv combine tab.
Worksheets("PPV Combine").Activate
ActiveSheet.Range("A1").Select
Selection.CurrentRegion.Select
Set mycells = Selection
'update source data and refresh ppv by vendor pivot
Worksheets("PPV by Vendor").Select
Range("B5").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address _
, Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub
This is the section resulting in an error:
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address)
Error:
15401
Code:
'updates source data and refreshes pivot tables
Sub Pivot_refresh()
'declare variables
Dim pt As PivotTable
Dim ws As Worksheet
Dim mycells As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''
' CC summary Pivot Table Update'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '
'Selects all of the data on ppv combine tab.
Worksheets("PPV Combine").Activate
ActiveSheet.Range("A1").Select
Selection.CurrentRegion.Select
'resizes selection to only include first 4 columns and assigns to variable mycells.
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
Set mycells = Selection
MsgBox (mycells.Address)
'update source data and refresh cc summary pivot
Sheets("CC summary").Select
Range("B5").Select
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address), _
Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Oracle-Pivot Table Update'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Selects current region on ppv-oracle tab
Worksheets("PPV-Oracle").Activate
ActiveSheet.Range("A2").Select
Selection.CurrentRegion.Select
'resizes the selection to exclude the top row and last column and assigns to variable mycells.
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
Set mycells = Selection
'update source data and refresh oracle -pivot
Worksheets("Oracle -Pivot").Select
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV-Oracle!" & mycells.Address _
, Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
' PPV by Vendory Pivot Table Update'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''
'Selects all of the data on ppv combine tab.
Worksheets("PPV Combine").Activate
ActiveSheet.Range("A1").Select
Selection.CurrentRegion.Select
Set mycells = Selection
'update source data and refresh ppv by vendor pivot
Worksheets("PPV by Vendor").Select
Range("B5").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address _
, Version:=xlPivotTableVersion15)
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub
This is the section resulting in an error:
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address)
Error:
15401