I would like to have a script that enters the dates on 1st row and work weeks
on second row as column headings.

Cell C1 has "8/31/09" (no fomula) and D1 has a formula "=C1+7", and E1
formula's "=D1+7".
Cell C2 has "WK35", D2 has "WK36", E2 has "WK37". They are text, no formula
here.

So if I run the script, value of D1 will be entered in cell C1, and D1:E1
will automatically be updated with value "9/14/09" and "9/28/09" because the
value in C1 changes.
And value in C2 will become "WK36", D2 becomes "WK37" and so on.

Thanks.

Hi,

You don't need vb code.

Leave you formula as they are in d1, e1 etc and put this formula in C2

="Wk " & WEEKNUM(C1)

Drag right as far as required. Entering a new date in C1 causes all the
formula to recalculate.

Mike


"choo" wrote:

I would like to have a script that enters the dates on 1st row and work weeks
on second row as column headings.

Cell C1 has "8/31/09" (no fomula) and D1 has a formula "=C1+7", and E1
formula's "=D1+7".
Cell C2 has "WK35", D2 has "WK36", E2 has "WK37". They are text, no formula
here.

So if I run the script, value of D1 will be entered in cell C1, and D1:E1
will automatically be updated with value "9/14/09" and "9/28/09" because the
value in C1 changes.
And value in C2 will become "WK36", D2 becomes "WK37" and so on.

Thanks.

Yes I know I can do this manually. The reason I want to use the code is
because there's more than one such heading in a worksheet.

C1:N2 has date and work week number, C3:N10 has data. Row 11 is blank.
Then C12:N13 has the same date/work week heading again, follow by data on
C14:N21. Row 22 is again blank.
Then repeat the heading again on C23:N24, data on C25:N32. And so on...


"Mike H" wrote:

Hi,

You don't need vb code.

Leave you formula as they are in d1, e1 etc and put this formula in C2

="Wk " & WEEKNUM(C1)

Drag right as far as required. Entering a new date in C1 causes all the
formula to recalculate.

Mike


"choo" wrote:

I would like to have a script that enters the dates on 1st row and work weeks
on second row as column headings.

Cell C1 has "8/31/09" (no fomula) and D1 has a formula "=C1+7", and E1
formula's "=D1+7".
Cell C2 has "WK35", D2 has "WK36", E2 has "WK37". They are text, no formula
here.

So if I run the script, value of D1 will be entered in cell C1, and D1:E1
will automatically be updated with value "9/14/09" and "9/28/09" because the
value in C1 changes.
And value in C2 will become "WK36", D2 becomes "WK37" and so on.

Thanks.

is there some formula for the week number?

SUB Amend()
Range("C1").Value = Range("D1").Value
Range("C2").Delete xlToLeft
END SUB







"choo" wrote:

I would like to have a script that enters the dates on 1st row and work weeks
on second row as column headings.

Cell C1 has "8/31/09" (no fomula) and D1 has a formula "=C1+7", and E1
formula's "=D1+7".
Cell C2 has "WK35", D2 has "WK36", E2 has "WK37". They are text, no formula
here.

So if I run the script, value of D1 will be entered in cell C1, and D1:E1
will automatically be updated with value "9/14/09" and "9/28/09" because the
value in C1 changes.
And value in C2 will become "WK36", D2 becomes "WK37" and so on.

Thanks.

There is no formula for week number in C2:N2.
The code - Range("C2").Delete xlToLeft - shifted the range to left is a good
idea. But this leaves N2 empty.



"Patrick Molloy" wrote:

is there some formula for the week number?

SUB Amend()
Range("C1").Value = Range("D1").Value
Range("C2").Delete xlToLeft
END SUB







"choo" wrote:

I would like to have a script that enters the dates on 1st row and work weeks
on second row as column headings.

Cell C1 has "8/31/09" (no fomula) and D1 has a formula "=C1+7", and E1
formula's "=D1+7".
Cell C2 has "WK35", D2 has "WK36", E2 has "WK37". They are text, no formula
here.

So if I run the script, value of D1 will be entered in cell C1, and D1:E1
will automatically be updated with value "9/14/09" and "9/28/09" because the
value in C1 changes.
And value in C2 will become "WK36", D2 becomes "WK37" and so on.

Thanks.