Shift values to the right on demand with button

[Lelewiwi]

Hello helpful people!

I am trying to cut and paste the value of multiple ranges of cells shifting them 8 columns to the right. Essentially every month, I want the user to click a button and the previous month's results will automatically shift to the right so that the current month's results can be entered. Specifically:

E22 moves to M22
M22 moves to U22
U22 moves to AC22
AC22 moves to AK22
AK22 moves to AS22

E25:E27 moves to M25:M27
M25:M27 moves to U25:U27
U25:U27 moves to AC25:AC27
AC25:AC27 moves to AK25:AK27
AK25:AK27 moves to AS25:AS27

I only need the values to move, and I want the formatting to stay the same. All other cells are formulas that run off these specific values. None of the cells are merged & centered, they are centered across cells. (I've heard that is an issue.)

This needs to be an on-demand action so a button would be best to simplify matters. I have never written code nor even looked at anything resembling code so please bear with me and give me simple instructions.

I am working on another button that will transfer the new data from another workbook as well. Would love it if one button could do it all! (Probably wishful thinking!)

 

[Micron]

If you place an ActiveX command button on the sheet, and if it's name is the same as below, then this should do the first part. You should be able to replicate for the other ranges? You could simply re-enter the Set statements where noted but make those ranges what you need in the second part.

Private Sub CommandButton1_Click()
Dim ary(1 To 5) As Range
Dim i As Integer

Set ary(1) = Range("E22")
Set ary(2) = Range("M22")
Set ary(3) = Range("U22")
Set ary(4) = Range("AC22")
Set ary(5) = Range("AK22")

For i = 5 To 1 Step -1
    ary(i).Offset(0, 8) = ary(i)
    ary(i) = ""
Next

'repeat the array Set statements with the next set of ranges then finish with:

For i = 1 To 5
    Set ary(i) = Nothing
Next

End Sub

 

[Lelewiwi]

That worked! Thank you so much!

 

[Micron]

Glad I could help & thanks for the recognition!