Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
I have a weekly status spreadsheet. Each week, I create a new sheet
with Monday's date on it. Then put in the necessary fields and formatting. I'm writing a macro to do this (talk about lazy!). If I have a sheet named "Mar 17", how can I use that to create a sheet named "Mar 24". I already copied the sheet name into a cell, and XL recognized it as a date, so I could just copy the name, paste it to a cell, add 7, then use that as the name for the new sheet. But is there a more direct way of doing this? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
Dave
One way: Sub TestDates() Dim NewShtName As String NewShtName = Format(CDate(ActiveSheet.Name) + 7, "mmm dd") Worksheets.Add(After:=Sheets(Sheets.Count)).Name = NewShtName End Sub HTH Otto "davegb" wrote in message ... I have a weekly status spreadsheet. Each week, I create a new sheet with Monday's date on it. Then put in the necessary fields and formatting. I'm writing a macro to do this (talk about lazy!). If I have a sheet named "Mar 17", how can I use that to create a sheet named "Mar 24". I already copied the sheet name into a cell, and XL recognized it as a date, so I could just copy the name, paste it to a cell, add 7, then use that as the name for the new sheet. But is there a more direct way of doing this? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
On Mar 24, 3:58*pm, davegb wrote:
I have a weekly status spreadsheet. Each week, I create a new sheet with Monday's date on it. Then put in the necessary fields and formatting. I'm writing a macro to do this (talk about lazy!). If I have a sheet named "Mar 17", how can I use that to create a sheet named "Mar 24". I already copied the sheet name into a cell, and XL recognized it as a date, so I could just copy the name, paste it to a cell, add 7, then use that as the name for the new sheet. But is there a more direct way of doing this? Thanks in advance. Run this macro with the sheet activated that you want to add 7 days to Sub NewSheet() Dim i As Variant Dim idate As Date idate = ActiveSheet.Name i = idate + 7 Sheets.Add ActiveSheet.Name = Format(i, "mmm dd") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
On Mar 24, 3:13*pm, "Otto Moehrbach"
wrote: Dave One way: Sub TestDates() * * * Dim NewShtName As String * * * NewShtName = Format(CDate(ActiveSheet.Name) + 7, "mmm dd") * * * Worksheets.Add(After:=Sheets(Sheets.Count)).Name = NewShtName End Sub HTH *Otto"davegb" wrote in message ... I have a weekly status spreadsheet. Each week, I create a new sheet with Monday's date on it. Then put in the necessary fields and formatting. I'm writing a macro to do this (talk about lazy!). If I have a sheet named "Mar 17", how can I use that to create a sheet named "Mar 24". I already copied the sheet name into a cell, and XL recognized it as a date, so I could just copy the name, paste it to a cell, add 7, then use that as the name for the new sheet. But is there a more direct way of doing this? Thanks in advance.- Hide quoted text - - Show quoted text - Thanks, that's exactly the number of ways I needed. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
On Mar 24, 3:32*pm, GTVT06 wrote:
On Mar 24, 3:58*pm, davegb wrote: I have a weekly status spreadsheet. Each week, I create a new sheet with Monday's date on it. Then put in the necessary fields and formatting. I'm writing a macro to do this (talk about lazy!). If I have a sheet named "Mar 17", how can I use that to create a sheet named "Mar 24". I already copied the sheet name into a cell, and XL recognized it as a date, so I could just copy the name, paste it to a cell, add 7, then use that as the name for the new sheet. But is there a more direct way of doing this? Thanks in advance. Run this macro with the sheet activated that you want to add 7 days to Sub NewSheet() Dim i As Variant Dim idate As Date idate = ActiveSheet.Name i = idate + 7 Sheets.Add ActiveSheet.Name = Format(i, "mmm dd") End Sub Thanks, very clever! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
On Mar 24, 4:45*pm, davegb wrote:
On Mar 24, 3:32*pm, GTVT06 wrote: On Mar 24, 3:58*pm, davegb wrote: I have a weekly status spreadsheet. Each week, I create a new sheet with Monday's date on it. Then put in the necessary fields and formatting. I'm writing a macro to do this (talk about lazy!). If I have a sheet named "Mar 17", how can I use that to create a sheet named "Mar 24". I already copied the sheet name into a cell, and XL recognized it as a date, so I could just copy the name, paste it to a cell, add 7, then use that as the name for the new sheet. But is there a more direct way of doing this? Thanks in advance. Run this macro with the sheet activated that you want to add 7 days to Sub NewSheet() Dim i As Variant Dim idate As Date idate = ActiveSheet.Name i = idate + 7 Sheets.Add ActiveSheet.Name = Format(i, "mmm dd") End Sub Thanks, very clever!- Hide quoted text - - Show quoted text - your welcome |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating new sheet named one week newer that active sheet
I recommand yyyymmdd format. That way the order of the files is sorted
by date when you read the XL files from a folder using Explorer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Names of named ranges in active sheet only | Excel Programming | |||
Names of named ranges in active sheet only | Excel Programming | |||
Creating a Dynamic Named Range Using Sheet Name and Column Header | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming | |||
Creating absolute references including active sheet name in the formula | Excel Programming |