Sheet names
 

I already have a subroutine that copies my active sheet, takes the
copied sheet and changes it's name to today's date in the format mm-dd-
yyyy. What I'd like to do is make the date = this week's Monday. No
matter where I am in the week. So if it's Thursday, March 1, I'd like
the vb to name the sheet 02-26-2007 (which was Monday's date) If it's
Sunday, I'd like to use the previous Monday. SheetExist is the common
function found in many places here in the group and on several sites.
I know I only need to manipulate the first line here, but how?
Any help greatly appreciated...
Thanks.
Rob


today_date = Format(Date, "mm-dd-yyyy")
i = 0
If SheetExist(today_date) Then
Do
i = i + 1
Loop Until SheetExist(today_date & "(rev " & i & ")") = False
ActiveSheet.Copy After:=Sheet5
ActiveSheet.Name = today_date & "(rev " & i & ")"
MsgBox "There is already a sheet with today's date on it... This
will show a 'rev' and the rev #."
Else
Activesheet.Copy After:=Sheet5
ActiveSheet.Name = today_date
End If

Sheet names
 

today_date = Format(Date, "mm-dd-yyyy") - Weekday(Date, vbTuesday)

"okrob" wrote:

I already have a subroutine that copies my active sheet, takes the
copied sheet and changes it's name to today's date in the format mm-dd-
yyyy. What I'd like to do is make the date = this week's Monday. No
matter where I am in the week. So if it's Thursday, March 1, I'd like
the vb to name the sheet 02-26-2007 (which was Monday's date) If it's
Sunday, I'd like to use the previous Monday. SheetExist is the common
function found in many places here in the group and on several sites.
I know I only need to manipulate the first line here, but how?
Any help greatly appreciated...
Thanks.
Rob


today_date = Format(Date, "mm-dd-yyyy")
i = 0
If SheetExist(today_date) Then
Do
i = i + 1
Loop Until SheetExist(today_date & "(rev " & i & ")") = False
ActiveSheet.Copy After:=Sheet5
ActiveSheet.Name = today_date & "(rev " & i & ")"
MsgBox "There is already a sheet with today's date on it... This
will show a 'rev' and the rev #."
Else
Activesheet.Copy After:=Sheet5
ActiveSheet.Name = today_date
End If

Sheet names
 

I think you need to change it slightly:

today_date = Format(Date - Weekday(Date, vbTuesday), "mm-dd-yyyy")


"Brad" wrote:

today_date = Format(Date, "mm-dd-yyyy") - Weekday(Date, vbTuesday)

"okrob" wrote:

I already have a subroutine that copies my active sheet, takes the
copied sheet and changes it's name to today's date in the format mm-dd-
yyyy. What I'd like to do is make the date = this week's Monday. No
matter where I am in the week. So if it's Thursday, March 1, I'd like
the vb to name the sheet 02-26-2007 (which was Monday's date) If it's
Sunday, I'd like to use the previous Monday. SheetExist is the common
function found in many places here in the group and on several sites.
I know I only need to manipulate the first line here, but how?
Any help greatly appreciated...
Thanks.
Rob


today_date = Format(Date, "mm-dd-yyyy")
i = 0
If SheetExist(today_date) Then
Do
i = i + 1
Loop Until SheetExist(today_date & "(rev " & i & ")") = False
ActiveSheet.Copy After:=Sheet5
ActiveSheet.Name = today_date & "(rev " & i & ")"
MsgBox "There is already a sheet with today's date on it... This
will show a 'rev' and the rev #."
Else
Activesheet.Copy After:=Sheet5
ActiveSheet.Name = today_date
End If

Sheet names
 

On Mar 1, 7:29 pm, JMB wrote:
I think you need to change it slightly:

today_date = Format(Date - Weekday(Date, vbTuesday), "mm-dd-yyyy")



"Brad" wrote:
today_date = Format(Date, "mm-dd-yyyy") - Weekday(Date, vbTuesday)

"okrob" wrote:

I already have a subroutine that copies my active sheet, takes the
copied sheet and changes it's name to today's date in the format mm-dd-
yyyy. What I'd like to do is make the date = this week's Monday. No
matter where I am in the week. So if it's Thursday, March 1, I'd like
the vb to name the sheet 02-26-2007 (which was Monday's date) If it's
Sunday, I'd like to use the previous Monday. SheetExist is the common
function found in many places here in the group and on several sites.
I know I only need to manipulate the first line here, but how?
Any help greatly appreciated...
Thanks.
Rob

today_date = Format(Date, "mm-dd-yyyy")
i = 0
If SheetExist(today_date) Then
Do
i = i + 1
Loop Until SheetExist(today_date & "(rev " & i & ")") = False
ActiveSheet.Copy After:=Sheet5
ActiveSheet.Name = today_date & "(rev " & i & ")"
MsgBox "There is already a sheet with today's date on it... This
will show a 'rev' and the rev #."
Else
Activesheet.Copy After:=Sheet5
ActiveSheet.Name = today_date
End If- Hide quoted text -

- Show quoted text -

Thanks Brad and JMB... Worked great...