Creating a new spreadsheet from a working spreadsheet on a weekly basis w/certain columns

I would like to create a spreadsheet from data in an existing one bringing over only certain columns, and have it update automatically when it is opened. This would be on a weekly schedule. The original spreadsheet contains much more data that we do not want vendors to see. The data in the new spreadsheet would need to be refreshed/updated cumulatively on a weekly basis. Can this be done with a macro and using Edit Links?

I am attaching a mock up of the original spreadsheet (column headings highlighted in yellow are those that need to be brought over into a new spreadsheet), and a mockup of the new spreadsheet.

Note that the "real" original spreadsheet is a bit of a mess because different people have had their hands on it and there are random worksheets in it that are hidden, etc. I have removed this stuff in my mockup and hopefully when all is said and done this messiness doesn't make a difference.

Any help would be greatly appreciated. Thank you