I have a worksheet that includes data on medical resident students. Each
student will rotate through 12 different departments in a year. Is there away
to create a macro that will automatically shift the departments for each
month so that the cell data shifts down one cell and to the right one cell?
The last cell in a row would need to move to the first cell in the next
column. Example:

Doctor Jan Feb Mar Apr May June
Smith IM Ortho Peds Neuro OB Surgery
Thomas Surgery IM

"Sue" wrote:

I have a worksheet that includes data on medical resident students. Each
student will rotate through 12 different departments in a year. Is there away
to create a macro that will automatically shift the departments for each
month so that the cell data shifts down one cell and to the right one cell?
The last cell in a row would need to move to the first cell in the next column. Example:

Doctor Jan Feb Mar Apr May June
Smith IM Ortho Peds Neuro OB Surgery
Thomas Surgery IM Ortho Peds Neuro OB

Sorry - I was not finished sending my example. Thank you for your help.

Hi Sue,

Does this do it

Sub MoveData()
Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sSave = Cells(iLastRow, 13).Value
Range("B2:M" & iLastRow).Cut Destination:=Range("C3")
Range("N3:N" & iLastRow + 1).Cut Destination:=Range("B2")
Range("C" & iLastRow + 1 & ":N" & iLastRow + 1).Cut
Destination:=Range("C2")

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sue" wrote in message
...
I have a worksheet that includes data on medical resident students. Each
student will rotate through 12 different departments in a year. Is there
away
to create a macro that will automatically shift the departments for each
month so that the cell data shifts down one cell and to the right one
cell?
The last cell in a row would need to move to the first cell in the next
column. Example:

Doctor Jan Feb Mar Apr May June
Smith IM Ortho Peds Neuro OB Surgery
Thomas Surgery IM