Copy named range 4 columns over repeatedly-Macro?

[StorminASU]

Hey Everyone,
I have a worksheet that is tracking payroll by department by bi-weekly payroll period. In one column (Column C for example) is the current payroll period payroll figure and in column G, as a comparison, is the prior year's figure for the equivalent period. There are 26 payroll periods running horizontally, with the departments listed vertically. At the end of each fiscal year, I would like to push a "magic button," which would move all of the independent current payroll periods to the similar prior period column which is 4 columns over for each period. I think I might have to make 26 macros and then just make a master button. Can anyone provide this macro extreme-noob with some help? TIA

 

[nuked]

You could try:

Sub RollYear()
Dim s As Variant
With ActiveSheet
For Each s In Array("E", "G")
    .Range(s & ":" & s).Offset(0, 4) = .Range(s & ":" & s).Value
    .Columns(s).ClearContents
Next s
End With
End Sub

Just replace/add to "E", "G" with the actual columns that you want to move forward.

ALT+F11
Insert=>Module
Paste the Code
Run from the Macro Menu

 

[StorminASU]

Thank you so much for the quick response.

How would I make sure "C" went to "G", then "L" to "Q", etc? Would I just copy this 26 times (moving the end sub to the bottom, not copying it of course)?

 

[StorminASU]

Also, I can't move the whole column over, I will only need to move rows 5-27 and rows 35-57. Is that still possible?

 

[StorminASU]

Ok, I'm very sorry to flood this thread, but I have gotten this far, just need some clarification if anyone has time.

Sub RollYear()Dim s As Variant[br]Dim resp As Long[br]resp = MsgBox(prompt:="Are you sure you want to clear rollover Current Year to Prior Year?", Buttons:=vbYesNo)[br]If resp = vbNo Then[br]Exit Sub[br]End If[br]
With ActiveSheetActiveSheet.UnprotectFor Each s In Range("c5:c27")    .Range(s & ":" & s).Offset(0, 4) = .Range(s & ":" & s).Value    .Range("c5:c27").ClearContentsNext sEnd WithExit SubWith ActiveSheetFor Each s In Range("l5:l27")    .Range(s & ":" & s).Offset(0, 5) = .Range(s & ":" & s).Value    .Range("l5:l27").ClearContentsNext sEnd WithEnd Sub

I need two subs because the first range is going to move 4 columns over, whereas every other range will move 5 columns over. Is there a way to do this in one sub?

 

[nuked]

Short answer, yes, but I'll have to look at it tomorrow from work. On linux atm.

 

[StorminASU]

Here is what I have so far, but it appears to be stopping after the first sub runs. Again, thanks so much for all your help.

Private Sub CommandButton1_Click()Application.ScreenUpdating = False
Dim s As Variant
Dim resp As Long
resp = MsgBox(prompt:="You are about to clear ALL current year payroll information.  Are you sure you want to continue?", Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If


resp = MsgBox(prompt:="Are you sure you want to complete the rollover from current year to prior year?", Buttons:=vbYesNo)
If resp = vbNo Then
Exit Sub
End If


With ActiveSheet
ActiveSheet.Unprotect
For Each s In Array("c5:c27", "c35:c57")
    .Range(s & ":" & s).Offset(0, 4) = .Range(s & ":" & s).Value
    .Range("c5:c27,c35:c57").ClearContents
Next s
End With


With ActiveSheet
For Each s In Array("l5:l27", "w5:w27", "ah5:ah27", "as5:as27", "bd5:bd27", "bo5:bo27", "bz5:bz27", "ck5:ck27", "cv5:cv27", "dg5:dg27", "dr5:dr27", "ec5:ec27", "en5:en27", "ey5:ey27", "fj5:fj27", "fu5:fu27", "gf5:gf27", "gq5:gq27", "hb5:hb27", "hm5:hm27", "hx5:hx27", "ii5:ii27", "it5:it27", "je5:je27", "jp5:jp27", "l35:l57", "w35:w57", "ah35:ah57", "as35:as57", "bd35:bd57", "bo35:bo57", "bz35:bz57", "ck35:ck57", "cv35:cv57", "dg35:dg57", "dr35:dr57", "ec35:ec57", "en35:en57", "ey35:ey57", "fj35:fj57", "fu35:fu57", "gf35:gf57", "gq35:gq57", "hb35:hb57", "hm35:hm57", "hx35:hx57", "ii35:ii57", "it35:it57", "je35:je57", "jp35:jp57")
    .Range(s & ":" & s).Offset(0, 5) = .Range(s & ":" & s).Value
    .Range("CurrentYearActual,CurrentYearOT").ClearContents


ActiveSheet.Protect


End With


End Sub

 

[StorminASU]

Nuked, I'm sorry to be a bother, but you've not had a chance to look at the code above have you?

 

[StorminASU]

Would anyone have time to take a look at the code above to see why it isn't progressing to the next sub?

Sorry I'm so new at this stuff, I'm sure it's something obvious I'm overlooking. TIA for the help!