After adding a new sheet, wanting to compare cell values

[Emmert]

Hello all,

Long time listener, first time caller.

I have an initial worksheet with today's date in cell C1 and the
worksheet name was created using this cell so my worksheets are listed
in date sequence. I also have vb code in a button that will add a new
worksheet, the same as the existing worksheet, using the current date
in format "mm-dd" as the worksheet name based on cell C1 (same as
before).

I wanted to know how to compare the cell value in the existing
spreedsheet with the newly created worksheet so the old sheet from
yesterday will display the date it was created instead of the current
date. Additionally, since the new worksheet will contain the same code,
how do I prevent the new worksheet from giving me errors since there is
nothing in front of it to compare. - Sorry if it sounds confusing.

 

[Guest]

if activesheet.Index <> 1 then
set sh = activesheet.previous
msgbox sh.name

 

[Emmert]

I tried your code but it didn't give me the output I wanted. I
actually figured it out. Thanks for the attempt.

Code:

Private Sub AddSheet_Click()
ActiveSheet.Copy After:=Sheets(Sheets.count)
ActiveSheet.Name = Format(Date, "mm-dd")

ActiveSheet.Previous.Range("c1") = Format(Date - 1)

End Sub

Thanks again,
Emmert