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."

 

[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.

 

[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.