Your a genius.

Many thanks

Terilad

"Jacob Skaria" wrote:

Try the below. Note the new line added to increment the date. Range("A10") =
Range("A10") + 7

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant

Range("A10") = Range("A10") + 7
arrData(0) = Range("A35")

For lngRow = 3 To 35 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("A" & lngRow)
Range("A" & lngRow) = arrData(intTemp - 1)
Next
Range("A1") = varValue
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"terilad" wrote:

Hi All,

I have the following macro in excel.

Sub Macro()
Dim lngRow As Long
Dim intTemp As Integer
Dim arrData(17) As Variant
arrData(0) = Range("A35")
For lngRow = 3 To 35 Step 2
intTemp = intTemp + 1
arrData(intTemp) = Range("A" & lngRow)
Range("A" & lngRow) = arrData(intTemp - 1)
Next
Range("A1") = varValue
End Sub

This to to rotate names through a 17 week shift rosta when macro is run.

What I need is I have a week commencing date in cell A10, e.g. 04 May 2009
is there a code I can add so that when the shift rotates when I run the macro
the date in Cell A10 can be replaced by the new week commencing date e.g. 11
May 2009
and so on when the macro is run.

Any help is greatfully appreciated.

Many thanks

Terilad