Macro to copy sheet

Peter1973

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

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top