How can I tell Excel to not reference macros and source data of old files?

I create a pivottable that references:
Sheet1!$A:$H.
Sometimes when I email that Excel file, or copy that Excel file, the pivottable source data will change to:
C:\path\[filename]Sheet1!$A:$H.
I rarely want this to happen. Can I write the source reference in another way that makes Excel understand that I always want it to point to "ThisWorkbook"?

The same this happens when I assign a macro to a button. I can assign the macro
CopyAllRows
But when I email or copy the file, sometimes this will turn into:
C:\path\[filename]CopyAllRows
I rarely want this to happen. Can I write the macro path in another way that makes Excel understand that I always want it to point to "ThisWorkbook"?

Here is a sample workbook:
Test - Copy.xlsx

Bump. Has anyone else experienced this?