Auto Sheet Names and Date cells

[Keith Patrick-Ward]

Hi,

I have an excel document which it is intended to have a sheet for each
work week. In each sheet I have cells that have the dates for Monday
through to Friday.
Everytime I create a new sheet (from a template sheet using the copy
sheet method) I have to name it manually and then change the date of
the Monday cell so that Tuesday to Friday can be calculated.

What I would like is for the the date columns for Monday to Friday to
be automatically calculated, and if possible the name of the sheet
too.


e.g. My sheet names are

12th - 16th Sept | 5th - 9th Sept | 29th Oct - 2nd Sept

these sheets have cells B2 - F2 which contain the dates and are
calculated as thus, B2 -- ='5th - 9th Sept'!F2+3 C2 -- = B2+1 etc
etc

So what suggestions to make this work so I can just slot a new sheet
in?

Thanks for any help

 

[Don Guillett]

Hi,

I have an excel document which it is intended to have a sheet for each
work week. In each sheet I have cells that have the dates for Monday
through to Friday.
Everytime I create a new sheet (from a template sheet using the copy
sheet method) I have to name it manually and then change the date of
the Monday cell so that Tuesday to Friday can be calculated.

What I would like is for the the date columns for Monday to Friday to
be automatically calculated, and if possible the name of the sheet
too.

e.g. My sheet names are

12th - 16th Sept | 5th - 9th Sept | 29th Oct - 2nd Sept

these sheets have cells B2 - F2 which contain the dates and are
calculated as thus,  B2 -- ='5th - 9th Sept'!F2+3   C2 -- = B2+1 etc
etc

So what suggestions to make this work so I can just slot a new sheet
in?

Thanks for any help

"If desired, send your file to dguillett1 @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Ron Rosenfeld]

Hi,

I have an excel document which it is intended to have a sheet for each
work week. In each sheet I have cells that have the dates for Monday
through to Friday.
Everytime I create a new sheet (from a template sheet using the copy
sheet method) I have to name it manually and then change the date of
the Monday cell so that Tuesday to Friday can be calculated.

What I would like is for the the date columns for Monday to Friday to
be automatically calculated, and if possible the name of the sheet
too.


e.g. My sheet names are

12th - 16th Sept | 5th - 9th Sept | 29th Oct - 2nd Sept

these sheets have cells B2 - F2 which contain the dates and are
calculated as thus, B2 -- ='5th - 9th Sept'!F2+3 C2 -- = B2+1 etc
etc

So what suggestions to make this work so I can just slot a new sheet
in?

Thanks for any help

I'm not sure from your description what it is that is in B2:F2, other than that they are dates.

But the following macro should give you some ideas as to how to proceed. It generates and names a new worksheet, based on the contents of B2 in your existing sheets, and then fills in B2:F2 with the appropriate dates.

As written, it uses standard THREE(3) letter abbreviations for dates. I note in your examples that you have mixed both three and four letter abbreviations. If that's what you really want, you will need to supply the individual abbreviations for each month.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

With more information, it would be easy to copy or program the complete template onto the new sheet.

======================================
Option Explicit
Sub AddNewWeekdaySheet()
Dim ws As Worksheet
Dim dDate As Date
Dim dLast As Date
Dim sLastWS As String
Const FirstDate As String = "B2"
Dim NewSheetName As String

'Determine Last Date
For Each ws In Worksheets
If IsDate(ws.Range(FirstDate)) Then
dDate = ws.Range(FirstDate).Value
dLast = IIf(dDate > dLast, dDate, dLast)
sLastWS = IIf(dDate >= dLast, ws.Name, sLastWS)
End If
Next ws

NewSheetName = OrdinalNum(Day(dLast + 7)) & Format(dLast + 7, " mmm") & _
" - " & OrdinalNum(Day(dLast + 11)) & Format(dLast + 11, " mmm")

Worksheets.Add after:=Worksheets(sLastWS)
ActiveSheet.Name = NewSheetName
With Worksheets(NewSheetName)
.Range(FirstDate).Value = dLast + 7
With .Range(.Range(FirstDate), .Range(FirstDate)(0, 5))
.DataSeries Type:=xlChronological, Date:=xlDay
.NumberFormat = "d mmm"
End With
End With
End Sub
'--------------------------------------------------
Private Function OrdinalNum(num) As String
Dim Suffix As String

OrdinalNum = num
If Not IsNumeric(num) Then Exit Function
If num <> Int(num) Then Exit Function

Select Case num Mod 10
Case Is = 1
Suffix = "st"
Case Is = 2
Suffix = "nd"
Case Is = 3
Suffix = "rd"
Case Else
Suffix = "th"
End Select

Select Case num Mod 100
Case 11 To 19
Suffix = "th"
End Select

OrdinalNum = Format(num, "#,##0") & Suffix
End Function
================================

 

[Keith Patrick-Ward]

A macro like this is just what I am after.

I'll try and explain again what I'm after.

each sheet has a row that contains columns for each day in the week.
The cell range of this row with dates is B2:F2 in each sheet, as
below:

A B C D E F
1
2 Date 19/09/11 20/09/11 21/09/11 22/09/11 23/09/11

Each sheet should have the name of the week i.e. 19 - 23 Sep, Don't
care too much what the title is as long as it is easy to read the
dates.

I have a sheet that contains a template of the info I need, so at
present I copy the sheet, rename it, then alter the formula in cell B2
to be

='Name of previous sheet'!F2+3

the formula's in C2, D2, E2 and F2 are

= B2+1, C2+1, D2+1, E2+1 respectively.

Thus my dates are calculated.

What I would like is to say run a macro that inserts a new sheet with
the template of info and then does the rename of the sheet and the
alteration of the formula in B2 so that the dates are calculated
correctly.

Thanks again.

 

[Ron Rosenfeld]

Thus my dates are calculated.

What I would like is to say run a macro that inserts a new sheet with
the template of info and then does the rename of the sheet and the
alteration of the formula in B2 so that the dates are calculated
correctly.

If I understand you correctly, the only problem with the macro I provided is that the format of the Date Labels in the various columns was not exactly what you wanted.

Is that correct?

 

[GS]

Ron Rosenfeld laid this down on his screen :

If I understand you correctly, the only problem with the macro I provided is
that the format of the Date Labels in the various columns was not exactly
what you wanted.

Is that correct?

Ron,
Since the OP uses a template for each new weekly sheet, there might be
some formatting and wks design features used. I'd be inclined to just
copy the template after the last sheet, rename it aptly, and put the
start date in the right place so existing formulas in the day columns
update themselves. does this make sense?

 

[Ron Rosenfeld]

Ron,
Since the OP uses a template for each new weekly sheet, there might be
some formatting and wks design features used. I'd be inclined to just
copy the template after the last sheet, rename it aptly, and put the
start date in the right place so existing formulas in the day columns
update themselves. does this make sense?

One could certainly do that. But in general, with that sort of problem, I prefer to hard code the information into the macro.
I don't have to worry about inadvertently altering the template.
And if I do decide to make changes at a later date, I can document the when and why more easily in a macro; and possibly even use the macro, with a few alterations, to go back and change the existing worksheets.

 

[GS]

Ron Rosenfeld wrote on 9/14/2011 :

One could certainly do that. But in general, with that sort of problem, I
prefer to hard code the information into the macro. I don't have to worry
about inadvertently altering the template. And if I do decide to make changes
at a later date, I can document the when and why more easily in a macro; and
possibly even use the macro, with a few alterations, to go back and change
the existing worksheets.

Fair enough! However, the OP doesn't provide the template details and
so makes it difficult to support your arg. I can see the point of going
this way using a 3rd party spreadsheet that doesn't support copying
existing sheets (ie: FarPoint's Spread.ocx), but why not use Excel's
features to save the coding? I don't discount your point about updating
older sheets but this is something I prefer to do with an update
routine if warranted. Clearly, it's a matter of personal preference
that has merit either way!

 

[Ron Rosenfeld]

Fair enough! However, the OP doesn't provide the template details and
so makes it difficult to support your arg. I can see the point of going
this way using a 3rd party spreadsheet that doesn't support copying
existing sheets (ie: FarPoint's Spread.ocx), but why not use Excel's
features to save the coding? I don't discount your point about updating
older sheets but this is something I prefer to do with an update
routine if warranted. Clearly, it's a matter of personal preference
that has merit either way!

We'll see what he posts back.

 

[Clif McIrvin]

We'll see what he posts back.

What I have done on occasion is to turn on the macro recorder and
manually re-create a template sheet from scratch. Then I go in and clean
up the recorded macro code and I have a macro-embedded worksheet
template. There may be "easier"(?) ways to do this, but at my
experience level it certainly works! I have at least a couple daily use
macros that check the target sheet, and if it was based off an out-dated
template it "auto-magically" updates the worksheet.. As Garry points
out, user preference makes a big difference.

 

[GS]

Clif McIrvin expressed precisely :

What I have done on occasion is to turn on the macro recorder and manually
re-create a template sheet from scratch. Then I go in and clean up the
recorded macro code and I have a macro-embedded worksheet template. There
may be "easier"(?) ways to do this, but at my experience level it certainly
works! I have at least a couple daily use macros that check the target sheet,
and if it was based off an out-dated template it "auto-magically" updates the
worksheet.. As Garry points out, user preference makes a big difference.

That's a pretty good way to go about it. Not sure, though, that I like
the way it defines single-cell named ranges!

I can testify that doing this in VB6 with Spread.ocx isn't so easy
because there's no macro recorder. Also, you can't 'copy' sheets and so
the idea of using templates is a nogo. Having done this sooo much
easier in Excel with pre-designed templates I wouldn't want to do it
any other way unless absolutely necessary.