Vba code needed to copy all data from Sheet1 and create new excel sheet with name as Test and paste the data by creating new worksheet name as copydata and close the new excel sheet after copying or if it can be also done without opening the new excel sheet then also great.

There is a button in the sheet which will do this function.

Book1.xlsm

Any help appreciated as I am a newbie to excel programming.