macro to edit links and change source

[Vantrav]

I am creating a cover sheet that will be copied into various excel spreadsheets. The excel spreadsheets have the exact same tabs and pivot tables, just different quantities. I need to create a macro that when the cover sheet is copied into the new workbooks the end user can click a button and the edit links and change source will occur and then have the source as the current workbook to new cover worksheet has been copied into.

I am having trouble with the macro and would appreciate some help in what I need to write in the script.

[rcm]

It would be helpful if you could provide a sample with non-senitive data in excel format

[Vantrav]

I require a macro that opens the edit line box and then selects the Change Source tab and then selects the file name from the list that is an exact match to the current workbook that is opened. The coversheet that is being copied into the new workbook is sourcing the original file. I need to change that source to the current workbook. I have placed a sample of one of the formulas below. The filename [Riley 49_Liam model takeoff v3_Cover sheet source file.xlsx] is from the original source and that is the name I require to be updated to the current workbook. Although you can manually click 'edit Links' and then click on 'Change Source' and then select the file, I am looking for an automated macro to do this function. I have 45 end users that will be required to do this task everyday and I would prefer a macro assigned to a button to do this task.

=GETPIVOTDATA("PrimaryQuantity",'[Riley 49_Liam model takeoff v3_Cover sheet source file.xlsx]Resources Pivot Table'!$A$1,"Resource","cc 0999_item: 6230B : Roof Tiles - Supply")