Changing The Sourcedata Of A Current Pivottable

  • 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:

    The section triggering the error is:

    .SourceData = Workbooks(FileName).Worksheets("Detail").Range(Cells(4, 10), Cells(LastRow - 18, 116)) _

    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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!