Cell data change

[terilad]

Hello,

I am looking for some help in solving an issue i have with a shift rosta.
I have 17 rows in excel whick relate to 17 shift persons
Cell A1 to A17 as follows

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q

What I am looking for is for on every thursday I need data in cell A17 to be
input into cell A1 and all the other cells to go down
1 cell, e.g. data from cell A1 to Cell A2 and so on, this is the data I need
to rotate no other data needs to rotate.

Can anyone give me some help on this.

Many thanks

terilad

 

[terilad]

This is great, thanks, is thre any way that this can be done automatically
when the excel sheet is opened on a Thursday for instance?

Many thanks

Terilad

 

[Jacob Skaria]

Sure you can..

Launch VBE using short-key Alt+F11. On the left treeview double click 'This
Workbook '. Drop down to get the Workbook open event
Paste the below code in Workbook Open event.


Private Sub Workbook_Open()
If Format(Date, "ddd") = "Thu" Then

Dim varValue
varValue = Range("A17")
Range("A17") = ""
Range("A1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = varValue


End If
End Sub

 

[terilad]

Thanks a lot for your help. just to further bother you, can this still be
done if there is a spare row of cells below each row. e.g. cell A1 to move to
Cell A3 and so on, data move 2 cells?

Many thanks


Terilad

 

[Jacob Skaria]

Repeat the insert line and adjust the Range accordingly. What you can do is
try the original macro with Step In mode. F8. You will see what happens and
then you can adjust the code accordingly

 

[terilad]

Hi jacob, I cant seem to get this to work to move 2 rows.
In cells A4 A6 A8 A10 A12 A14 A16 and so on until A36 I have text Relief and
need these to be fixed to these cells, Cells A3 A5 A7 A9 A11 and so on until
A35 these are the cells that contain the staff name and these are the cells
that I require to rotate, I have tried some alternatives but it moves borders
down the page and removed borders from the cells that are rotating.

Do you have any ideas, I appreciate your help.

Regards


Terilad

 

[Jacob Skaria]

Try the below and feedback

Private Sub Workbook_Open()
If Format(Date, "ddd") = "Thu" Then

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 If

End Sub


If this post helps click Yes

 

[terilad]

Many thanks Jacob for your help on this, works a treat.

Many thanks

Terilad