PivotTable field name is not valid when changing source data

[qitjch]

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

[p45cal]

As you step through the code with F8 and get to the line:
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
does the top row of that selection contain invalid headers such as a completely blank cell?

BTW,
Selection.Resize(Selection.Rows.Count, Selection.Columns.Count - 1)
can be shortened to:
Selection.Resize(, Selection.Columns.Count - 1)

and you should also be able to replace:
ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"PPV Combine!" & mycells.Address), _

with:

ActiveSheet.PivotTables("PivotTable5").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
mycells, _