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:

Please Login or Register  to view this content.

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.