Name rotation macro

Question

Hi

I have the following macro to rotate names through a shift rosta, moving 2 cells at a time and the bottom one moving back to the top, I am looking for help to edit this so that from cell C35 will move 4 spaces to cell C39, so as when the rotation macro is run name in cell C39 will then move up to cell C5.  So all names from cell C5 to C35 will jump 2 rows and C35 will jump 4 rows.

Can anyone help me on this code?

Many thanks

Mark

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = Range("S2").Address Then

Range("A1").Select

If MsgBox("Do you want to rotate shift", vbYesNo + vbInformation, "Galashiels Operational Resources  © M Neil      ") <> _

vbYes Then Exit Sub

Dim lngRow As Long

Dim intTemp As Integer

Dim arrData(17) As Variant

Sheets("Galashiels Resources").Unprotect Password:="1257"

Range("N2") = Range("N2") + 7

Range("D4") = Range("D4") + 7

Range("F4") = Range("F4") + 7

Range("H4") = Range("H4") + 7

Range("J4") = Range("J4") + 7

Range("L4") = Range("L4") + 7

Range("N4") = Range("N4") + 7

Range("P4") = Range("P4") + 7

arrData(0) = Range("C37")

For lngRow = 5 To 37 Step 2

intTemp = intTemp + 1

arrData(intTemp) = Range("C" & lngRow)

Range("C" & lngRow) = arrData(intTemp - 1)

Next

Range("C1") = varValue

Range("D6:Q6").ClearContents

Range("D8:Q8").ClearContents

Range("D10:Q10").ClearContents

Range("D12:Q12").ClearContents

Range("D14:Q14").ClearContents

Range("D16:Q16").ClearContents

Range("D18:Q18").ClearContents

Range("D20:Q20").ClearContents

Range("D22:Q22").ClearContents

Range("D24:Q24").ClearContents

Range("D26:Q26").ClearContents

Range("D28:Q28").ClearContents

Range("D30:Q30").ClearContents

Range("D32:Q32").ClearContents

Range("D34:Q34").ClearContents

Range("D36:Q36").ClearContents

Range("D38:Q38").ClearContents

Range("D41:Q41").ClearContents

Range("B48:Q48").ClearContents

Range("D6:Q6").Interior.ColorIndex = xlNone

Range("D8:Q8").Interior.ColorIndex = xlNone

Range("D10:Q10").Interior.ColorIndex = xlNone

Range("D12:Q12").Interior.ColorIndex = xlNone

Range("D14:Q14").Interior.ColorIndex = xlNone

Range("D16:Q16").Interior.ColorIndex = xlNone

Range("D18:Q18").Interior.ColorIndex = xlNone

Range("D20:Q20").Interior.ColorIndex = xlNone

Range("D22:Q22").Interior.ColorIndex = xlNone

Range("D24:Q24").Interior.ColorIndex = xlNone

Range("D26:Q26").Interior.ColorIndex = xlNone

Range("D28:Q28").Interior.ColorIndex = xlNone

Range("D30:Q30").Interior.ColorIndex = xlNone

Range("D32:Q32").Interior.ColorIndex = xlNone

Range("D34:Q34").Interior.ColorIndex = xlNone

Range("D36:Q36").Interior.ColorIndex = xlNone

Range("D38:Q38").Interior.ColorIndex = xlNone

Range("D41:Q41").Interior.ColorIndex = xlNone

Range("B48:Q48").Interior.ColorIndex = xlNone

Sheets("Galashiels Resources").Protect Password:="1257", AllowFormattingCells:=True

End If

End Sub

Answer 1

I have uploaded a workbook that will export the worksheet without the VBA code. (Link below). 

I have renamed this workbook so that the name of the workbook with the VBA code is significantly different from the workbooks without the code otherwise it will create confusion. You can re-name it again to anything you like but you will need to edit the code at the following line to reflect the new name in  the Sub btnRotateShifts_Click()

strNewFileName = "VBA Code for Resources " & Format(dteWk, "dd-mmm-yy") & ".xls"

I have had to change the code workbook a bit and move the code to a standard module because if a worksheet is exported, any code in its VBA module gets automatically exported with it. When the code is in a standard module, the worksheet can be exported without the code.

I have reduced the workbook to just one sheet. The buttons to run the code are now Forms controls which are created from the forms Toolbar. Should not make any difference to you.

To see the VBA code use Alt/F11. If the code is not displayed then double click Module1 in the Project Explorer in the left column.

Click the button to rotate the shifts first. (The workbook with the code is automatically saved after the rotation). Then click the button to export the worksheet.  When the sheet is exported, the new workbook is saved and a MsgBox telling you that it is the workbook for distribution. After you click OK to the message, the workbook with the code is automatically closed and the new workbook is left displayed.

File deleted. See later post for new file upload

Answer 2

I am still in the dark. In your original post you said "from cell C35 will move 4 spaces to cell C39" Your example moves C37 to C39 which is only 2 spaces. You have inserted 2 complete rows and then you have different information across the row 39 which is a newly created row.

It is approaching my bed time so I will have another look at it tomorrow.

Answer 3

Hi OssieMac

Please find the link for the file, as you can see between the 2 worksheets, what I want the code to do for the resources new.

http://sdrv.ms/YPNL96

Many thanks

Mark

Answer 4

This is a re-post of a question that you have asked previously and I did not understand then and I still don't understand.

Can you upload a workbook to Skydrive. If you have sensitive data like peoples names etc then make a copy and replace the names with letters of the alphabet or something.

Best if you can have one worksheet with existing data and an example on another worksheet with what you want it to look like when finished.

Best to zip the file before posting. Below is a link for how to post on Skydrive. There is a link in step 1 to open Skydrive. Use the same login name and password that you use for this site.

After uploading the file right click it and select sharing. Don't fill in the form; select "Get a link" in the left column and then Create for others to read. Copy the returned link (which is highlighted) and paste into your post on this forum.

http://windows.microsoft.com/en-US/skydrive/change-access-permissions-faq