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.

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" <(E-Mail Removed)> wrote in message
news:38c007fa-6aaf-4463-8035-(E-Mail Removed)...
>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.

On Mar 24, 3:58*pm, davegb <daveg...@comcast.net> 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

On Mar 24, 3:13*pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <daveg...@comcast.net> wrote in message
>
> news:38c007fa-6aaf-4463-8035-(E-Mail Removed)...
>
>
>
> >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.

On Mar 24, 3:32*pm, GTVT06 <gtv...@hotmail.com> wrote:
> On Mar 24, 3:58*pm, davegb <daveg...@comcast.net> 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!

On Mar 24, 4:45*pm, davegb <daveg...@comcast.net> wrote:
> On Mar 24, 3:32*pm, GTVT06 <gtv...@hotmail.com> wrote:
>
>
>
>
>
> > On Mar 24, 3:58*pm, davegb <daveg...@comcast.net> 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

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.