Macro to copy sheet

[Peter1973]

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.

 

[VoG]

With the original workbook open perhaps this: you will need to change the path to the path to your desktop

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