| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheet Referencing - autofilling sheet names | Excel Worksheet Functions | |||
| Cell names = sheet names | Excel Worksheet Functions | |||
| Using Sheet names & Workbook names in VBA coding | Excel Programming | |||
| I want to print out the sheet tabs (sheet names) | Excel Worksheet Functions | |||
| return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
Linear Mode
