Macro to copy sheet

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
963
Office Version
  1. 365
Im trying to get a macro to copy a sheet from a work book as a seperate workbook I need it to copy and paste values and then save this to the desktop on the pc.

All the details are as follows :
Origional work book ROTA 2011-2012
Worksheet required to copy in new workbook = UPLOAD1

I need the new workbook to only have this one work sheet in it so delet the unused other 2 sheets, I also need it to be save to the desktop as the name UPLOAD1 and if possible with tomorrows date in the name but that is a wish more than a requirment.

I have tried recording the macro to do this but cant get anything to work, any help appreciated.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
With the original workbook open perhaps this: you will need to change the path to the path to your desktop

Rich (BB code):
Sub CopySheet()
Sheets("UPLOAD1").Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
ActiveWorkbook.SaveAs Filename:="C:\UPLOAD1 " & Format(Date + 1, "ddmmyyyy") & ".xls"
ActiveWorkbook.Close False
End Sub
 
Upvote 0

Forum statistics

Threads
1,222,095
Messages
6,163,901
Members
451,865
Latest member
dunworthc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top