Selecting previous worksheet

[Carolyne]

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

 

[Tom Ogilvy]

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

 

[Nikos Yannacopoulos]

Carolyne,

Try this:

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

pd$ = Format(Now() -1, "dd.mm.yy")
Sheets(pd$).Select
Cells.Select

Note: this will only work for looking up yesterday, it won't work for
looking up Friday on a Monday! If that is going to be the case, then it will
require a couple more lines of code:

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

If Weekday(Now(),2) = 1 Then
pd$ = Format(Now() -3, "dd.mm.yy")
Else
pd$ = Format(Now() -1, "dd.mm.yy")
End If
Sheets(pd$).Select
Cells.Select

HTH,
Nikos

 

[Don Guillett]

see if this helps. But, why not just copy the sheet and re-name it?
Sub previoussheet()
Sheets(ActiveSheet.Index - 1).UsedRange.Copy Range("a1")
End Sub

 

[yogendra joshi]

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

 

[yogendra joshi]

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