I'm trying to change the sourcedata of an existing pivottable. The sourcedata it uses is from another excel workbook that I have to update every month and each month the new file will be a different filename (to reflect the proper month). I wrote this macro that will ask the user for the file, open it, and set the source data to the proper range. I keep getting this very annoying error:
Run-time error '1004':
The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.
Here is the code:
Sub ImportNewSource()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FilePath As Variant
Dim ThisPivot As PivotTable
Dim FileName As String
Dim ShtNum As Integer
Dim LastRow As Long
ShtNum = ThisWorkbook.Sheets.Count
If ShtNum <> 31 Then
Beep
MsgBox "Error: Too many sheets in workbook, please delete any sheets that you added to this file then try again.", _
vbCritical = vbOKOnly, "Warning!"
Exit Sub
End If
Set ThisPivot = ThisWorkbook.Worksheets(16).PivotTables(1)
Filt = "Excel Files (*.xls), *.xls," & _
"All Files (*.*),*.*"
FilterIndex = 1
Title = "Select the monthly MBR file you wish you import data from"
FilePath = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)
If FilePath = False Then
MsgBox "Cancelled"
Exit Sub
Else
FileName = FileNameOnly(FilePath)
End If
If WorkbookIsOpen(FileName) Then
Beep
MsgBox "MBR file is currently open! Please close the file and try again.", _
vbCritical = vbOKOnly, "File is already open!"
Exit Sub
End If
Workbooks.Open FilePath, UpdateLinks:=False
If SheetExists("Detail") = False Then
ActiveWorkbook.Close SaveChanges:=False
Beep
MsgBox "Invalid PivotTable data in worksheet.", _
vbCritical = vbOKOnly, "Invalid Data"
Exit Sub
Else
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
End If
With ThisPivot
.SourceData = Workbooks(FileName).Worksheets("Detail").Range(Cells(4, 10), Cells(LastRow - 18, 116)) _
.Address(ReferenceStyle:=xlR1C1)
.RefreshTable
End With
Set ThisPivot = Nothing
End SubThe section triggering the error is:
.SourceData = Workbooks(FileName).Worksheets("Detail").Range(Cells(4, 10), Cells(LastRow - 18, 116)) _
.Address(ReferenceStyle:=xlR1C1)I'm completely stumped by this because to check myself, I've recorded a macro doing the same procedure and the range its using is under the worksheet entitled "Detail" and from R4C10:R206:C116 which is the exact R1C1 reference shown in the above code (When I run MsgBox LastRow to check, it comes up with Row 224). Anyone have any idea why this is happening? Thanks in advance for any help!