Creating new sheet named one week newer that active sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davegb - 24 Mar 2008 21:58 GMT

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.

Reply to this Message

Otto Moehrbach - 24 Mar 2008 22:13 GMT

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

>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
[quoted text clipped - 5 lines]
> a more direct way of doing this?
> Thanks in advance.

Reply to this Message

davegb - 24 Mar 2008 22:36 GMT

On Mar 24, 3:13 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
wrote:

> Dave
> One way:
[quoted text clipped - 15 lines]
>
> - Show quoted text -

Thanks, that's exactly the number of ways I needed.

Reply to this Message

GTVT06 - 24 Mar 2008 22:32 GMT

> 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
[quoted text clipped - 5 lines]
> 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

Reply to this Message

davegb - 24 Mar 2008 22:45 GMT

> > 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
[quoted text clipped - 17 lines]
> ActiveSheet.Name = Format(i, "mmm dd")
> End Sub

Thanks, very clever!

Reply to this Message

GTVT06 - 24 Mar 2008 23:00 GMT

> > > 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
[quoted text clipped - 21 lines]
>
> - Show quoted text -

your welcome

Reply to this Message

gimme_this_gimme_that@yahoo.com - 25 Mar 2008 01:21 GMT

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 to this Message