Add yesterday's date in sheet tab

[Mötley]

Hello, is it possible to add previous date in a sheet tab? I'm using excel 2007, and this is the code I'm using when inserting current date.

Sub UusiSivu()
ActiveWorkbook.Unprotect
Dim CurrentDay As Integer
Dim NewName As String
Dim WS As Worksheet
Set WS = ActiveSheet
If IsNumeric(Right(WS.Name, 2)) Then
   CurrentDay = Right(WS.Name, 2)
ElseIf IsNumeric(Right(WS.Name, 1)) Then
   CurrentDay = Right(WS.Name, 1)
Else
   Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Format(Date, "dd.mm.yyyy")
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
'Copies the current sheet to the end of the workbook
    Sheets("Default").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = NewName
    Range("D2") = NewName
  Dim oleObj As OLEObject
Else
   Set checkWs = Nothing
   MsgBox "Uusi taulukko voidaan lisätä huomenna."
End If
ActiveWorkbook.Protect
End Sub

 

[Mötley]

Nevermind, just changed this "NewName = Format(Date, "dd.mm.yyyy")" into "NewName = Format(Date -1, "dd.mm.yyyy")"

I'm a rookie in VBA so this was a big hit for me :D

 

[Mötley]

I still need help.. I was wrong, it still gives me current date I want it to add yesterday's date, how am I gonna do this?

 

[villy]

Is this helpful?:

Sub UusiSivu()
ActiveWorkbook.Unprotect
Dim CurrentDay As Integer
Dim NewName As String
Dim WS As Worksheet
Set WS = ActiveSheet
If IsNumeric(Right(WS.Name, 2)) Then
   CurrentDay = Right(WS.Name, 2)
ElseIf IsNumeric(Right(WS.Name, 1)) Then
   CurrentDay = Right(WS.Name, 1)
Else
   Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Day(Date) - 1 & "." & Month(Date) & "." & Year(Date)
'NewName = Format(Date, "dd.mm.yyyy")
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
'Copies the current sheet to the end of the workbook
    Sheets("Default").Copy after:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = NewName
    Range("D2") = NewName
  Dim oleObj As OLEObject
Else
   Set checkWs = Nothing
   MsgBox "Uusi taulukko voidaan lisätä huomenna."
End If
ActiveWorkbook.Protect
End Sub

 

[Mötley]

Thanks for reply! It does work, I insert code into commandbutton, I press once it gives me current date, I press twice it gives me yesterday. Is it possible that it gives ONLY yesterdays date? Current date is attached into another commandbutton.

I just realized, I have those two commandbuttons, one has current date and another has yesterday. When I press Current date-button FIRST, it gives me 10.6.2011 (correct?), then I press yesterday-button, it gives yesterday (correct?). BUT If I press yesterday-button FIRST, it gives me current AND yesterday's date.

I think this is not a problem anymore, but if its possible that yesterday-button gives me ONLY yesterday, I would be grateful. But anyway, code works. Thanks!

 

[villy]

Check that your Yesterday's button doesn't have the code calling the current date's command.

 

[Mötley]

Ummhhh... a little help please? I'm not so familiar with these codes :/

 

[villy]

Would you mind me asking, what is your currentday doing in your code?

 

[Mötley]

You see the code I posted above, I've created a calendar that inserts worksheets and renames them with current date. Let's say I've added 10.4.2011, then current day 10.6.2011. Problem with code is that it always gives current date, so I can't insert 10.5.2011. That's way I'm trying to modify this code so that I can go back in time.

Like I said, I'm not so familiar with these codes