Copy Cells to Separate Workbook Using VBA

Hello,

I am attempting to take a range of data, in this case A1:I42, and copy it to a separate workbook. So I want to copy A1:I42 from Sheet 1 in Workbook 1 to Sheet 1 (Change the name of Sheet 1 to the date at the time it was copied) Workbook 2. What I would like to do is the following:

Assign the code to a button to run everything.

1. Copy the data
2. Open workbook 2
3. Create a sheet on the separate workbook named with the date from when the original sheet was created (Sheet 1 on Workbook 2 would be changed to 7/7/18 for example)
4. Paste the data
5. Save the separate workbook and close it

I would be doing that process about 5 times per week and would like subsequent sheets created and moved to the end of the current number of sheets. That way the sheets will progress in chronological order (7/7/18, 7/8/18, 7/9/18, et cetera)

I would like to leave the data on the original sheet until it's manually deleted so it would stop there. I've looked at a variety of ways to do this but I can't get everything I want it to do actually done. Can anyone tell me what code I can use to accomplish all of those things?

Sincerely,

ProWHP