How do I create a macro that copies data from one excel file to another and saves it as a new name?

[Shmelky]

I have a file called "!PaymentTemplate.xlsx" and I have a folder called "Output" that contains many files.

I am trying to create a macro that opens up the "!PaymentTemplate.xlsx" file, then opens up one file from the "Output" folder, copies and pastes the data from that second file into the Template file and then saves it as a new name. I would like this process repeated for each of the unique files in the Output folder. I do not want the data appended together, each unique file gets pasted in the blank Template file. So if I have 10 files in the Output folder, I would have 10 new saved files.

Attached is an example of two files, MP2 and Sunrise each resulting in a new file MP2_New and Sunrise_New. The Template file was opened for each one separately, data pasted in, and then resaved.

I am not trying to do anything fancy, more just automate copying and pasted many files.

Thank you for your help and guidance.

[#2]

@Shmelky 

I will admit to a bias against macros, so take what I say with a grain (or lump, as you see fit) of salt.

What you're describing may be totally unnecessary, and certainly could be a failure to take advantage of Excel's ability to manipulate data via some of its many functions. Using a macro to copy and paste (when in fact Excel could simply retrieve the data into that template) is using what I'd call a "brute force" method instead of taking advantage of the elegance of the software.

Since you say you're not interested in "anything fancy," I'll leave it at that. If it's possible you'd be open to some alternative solutions, let us know. Or perhaps somebody else without my bias against macros will write that macro for you.

[#3]

I totally agree and I actually had all this done in Knime... BUT.... The reason why I am going the Macro route is because I upload data to a site where I need to use their Template file. It is protected in such a way that I can paste data in , but the site knows when I use that file versus another identical file. That is why I need to open the Template file, and paste data into it, and then resave it as a new name.