Macro to copy sheet

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
939
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,206
Members
430,196
Latest member
rez5656

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
Top