Change source data of existing PivotTable

I'm trying to write a macro that will gather raw data from any file you select and create a PivotTable. Trouble is, the workbook that obtains the macro already has a PivotTable in it. I don't want to delete the PivotTable and build another from scratch via code, all I'd like is for the user to select the file with the data (a new workbook is created every month that has the new data) and simply use that as the source data for the PivotTable already in place. I think I've almost got it, I've got all the error checks in place except for the very last line of code I get the "Object Required" error message although (I believe) I've already got the Object in place. Heres the code and all of the functions it uses:

Private Function SheetExists(sname) As Boolean
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function
Private Function FileNameOnly(pname) As String
    FileNameOnly = Dir(pname)
End Function
Private Function WorkbookIsOpen(wbname) As Boolean
    Dim z As Object
    On Error Resume Next
    Set z = Workbooks(wbname)
    If Err = 0 Then WorkbookIsOpen = True _
        Else WorkbookIsOpen = False
End Function
Sub ImportNewSource()
    Dim Filt As String
    Dim FilterIndex As Integer
    Dim Title As String
    Dim FilePath As Variant
    Dim SourceRng As Range
    Dim DirectorBook As Object
    Dim FileName As String
    Dim ShtNum As Integer
    
    Set DirectorBook = ThisWorkbook
    
    ShtNum = ActiveWorkbook.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
    
    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
    End If
    
    Set SourceRng = Workbooks(FileName).Sheets("Detail").Range("J4").CurrentRegion.Address(ReferenceStyle:=xlR1C1).Offset(3, 0) _
    .Resize(SourceRng.Rows.Count - 3, SourceRng.Columns.Count)

    DirectorBook.Sheets(16).PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    FilePath & "Detail!" & SourceRng

    DirectorBook.Sheets(16).PivotTables(1).RefreshTable

End Sub

As always, your help is very much appreciated!

EDIT: I tried changing the code some and the new code is reflected above. Now I get "Run-time error '91': Object variable or With block variable not set" triggered by the code "Set SourceRng"

fecurtis,

Please read forum rules below and then add the link to the cross post.

VBA Noob

Per the rules, this thread can also be found here:

http://www.ozgrid.com/forum/showthread.php?t=92472

While not new to forums, I am new to forums related to VBA programming. Never really thought about cross posting and I should've read all the rules other than the first couple since yeah I'd be peeved if I worked for a while on a code to help someone only to learn that it was already solved a while ago by someone else on another forum.

My mistake, won't happen again. Sincerest apologies. Thankfully the problem was caught before someone did spend a while trying to figure out my issue only to learn that its already been solved.