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 Sub
The section triggering the error is:
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!