Rotating work pattern

[LaDdIe]

Respected Group Members,

I have developed a annual employee leave taken worksheet on a row to month,
and it's fine upto a point where I need to copy this for another team on a
different work pattern,
I.E. We have four teams & four work patterns, If for example in the current
week team A is on pattern 1 (mon,tue,wed,thu,Off,Off,Off) team B will be on
pattern 2(Off,tue,wed,Off,fri,sat,Off) team C will be on pattern
3(mon,tue,wed,thu,Off,Off,Off) team D will be on pattern 4
(Mon,Off,Off,Thu,fri,sat,Off), then for the following week the patterns
rotate, Team A pattern 2, team B pattern 3, team C pattern 4 & team D pattern
1, and so on.
Is to possible to create VB code and how that will shift cell values,
formatting, list by rotation, I.E. A1 A2 A3 A4, C1 C2 C3 C4, E1 E2 E3 E4, G1
G2 G3 G4 ,so on one rotation A1 will become everything that C1 was, and C1
will become everything that E1 was and G1 will become everything that A1 was.
I can do it maually but its very time consuming.

Sorry that my example is long & thanks if anyone can help.

 

[Mike]

Hi,

For short-rotation patterns such as this the key to solving your problem
doesn't lie within VB, you need to establish the rotation period and in this
case it's 28 days. Having done that it's very simple to do the first 4 weeks
and then copy and paste. In that way I was able to produce a 1 yr rota for
your pattern in a couple of minutes. The first 4 weeks are pasted below.

Mike

M t w t f s s

A W W W W R R R
B R W W R W W R
C W W W W R R R
D W R R W W W R

m t w t f s s

W R R W W W R
W W W W R R R
R W W R W W R
W W W W R R R

M t w t f s s

W W W W R R R
W R R W W W R
W W W W R R R
R W W R W W R


m t w t f s s

R W W R W W R
W W W W R R R
W R R W W W R
W W W W R R R

 

[LaDdIe]

Thanks, sort of.