Copy range of cells from excel file on SharePoint

Background:
I have a master Excel file that needs updating from various people in various locations. To facilitate the updates and to provide version control, I broke the master excel file into separate smaller files and uploaded them onto SharePoint.

Desired Outcome:
My end result would be to have a button/macro for each worksheet that would reach out to the corresponding file on SharePoint, Copy the relevant cell(s), and PasteSpecial(Values) into the master Excel File.

Progress thus far:
I am able to directly link my master file to the file on SharePoint to reflect the values:

Please Login or Register  to view this content.

the issue with that is my master file updates automatically upon opening and is time consuming especially when there's only one worksheet (out of many) that needs updating. Can anyone provide guidance on the VBA needed to access the broken down files, copy a specific range and (preferably) Paste Special(values) into the master file?

Thanks for the suggestion. I copied the code and modified it as necessary to test an initial copy/paste. As it stands now, it hangs up and throws an error at the line:

Please Login or Register  to view this content.

I've verified the SharePoint link and tested it via windows explorer and it opens that way. I've verified the worksheet names (still Sheet1, Sheet2, etc). Range now is copying cell A1 from the source file to cell A1 of the destination file. Any ideas why it might be hanging up at this point?