The difference between other suggestions and mine is...

1. This code is making sure that the sheet is added before the first
If we just add a sheet, it will be added before the current active
sheet
2. I am assuming that there can be another sheet with name Sheet 1
existing in which case, wrong sheet will be selected for processing.

Mine code may or may not be exactly suitable, so just clarified,
not to undermine the other replies.

Also, I strongly suggest putting an error handling for checking
existance of the sheet before adding it.

HTH

yogendra joshi wrote:

Try this...

The logic is...

1. Create the new sheet before the first sheet in the workbook
2. Now copy the data from second sheet (previousely first sheet)
3. Paste

Hope this helps...

Sub add_new()
d$ = Format(Now, "dd.mm.yy")
Set sht = Sheets.Add(Sheets(1))
sht.Name = d$
Sheets(2).Cells.Copy
'Cells.Copy
sht.Paste
Application.CutCopyMode = False
End Sub



Carolyne wrote:

I'm trying to create a macro that creates a new worksheet named todays
date and copys the whole of the previous sheet into it. I can only
seem to select the specific sheet name does anyone know how to select
the previous sheet.
I have this so far

d$ = Format(Now, "dd.mm.yy")
Sheets.Add
Sheets("sheet1").Select
Sheets("Sheet1").Name = d$

The line below is where i want to select the previous sheet.

Sheets("12.05.04").Select
Cells.Select