Auto update/fill column heading

[choo]

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.

 

[Mike H]

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]

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

 

[Patrick Molloy]

is there some formula for the week number?

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

 

[choo]

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.