How to Copy Column and Paste Values to a New Column Every Time Macro Is Initiated

Question

I have a spreadsheet that calculates variances in column Z. At the end of the month I would like to copy and paste the values into another column in the same spreadsheet to keep track of the variances month to month.

I have a macro to copy from column Z to column BK.

I would like that each time I run the macro, to copy the values from column Z and paste it into a new column using the following schedule:

• Month 1 = Values Should be Pasted in Column BK
• Month 2 = Values Should be Pasted in Column BL
• Month 3 = Values Should be Pasted in Column BM
• Month 4 = Values Should be Pasted in Column BN
• Month 5 = Values Should be Pasted in Column BO
• Month 6 = Values Should be Pasted in Column BP
• Month 7 = Values Should be Pasted in Column BQ
• Month 8 = Values Should be Pasted in Column BR
• Month 9 = Values Should be Pasted in Column BS
• Month 10 = Values Should be Pasted in Column BT
• Month 11 = Values Should be Pasted in Column BU
• Month 12 = Values Should be Pasted in Column BV

After the 12th iteration, I would like the values from column Z to be copied into Column BK (the starting point). I believe this can be done using a loop?

I am having a difficult time coming up with the loop logic/coding.

Sub copyCurrentToPrevious()

    Dim ans As String

    On Error Resume Next

    Application.ScreenUpdating = False

    Sheets("Direct Materials").Activate

    ans = MsgBox("Are you sure you want to copy Previous Month Variance to YTD Variance Tracking?  This action can not be undone." & vbNewLine _
      & vbNewLine & "Select Yes to proceed with the copy/paste operation or Select No to cancel.", vbYesNo + vbExclamation, "Product Costing")

    If ans = vbNo Then Exit Sub

    Range("Z9:Z220").Copy
    Range("BK9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

    Range("Z226:Z306").Copy
    Range("BK226").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

    Range("Z311:Z471").Copy
    Range("BK311").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

    Range("Z476:Z524").Copy
    Range("BK476").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

    Application.CutCopyMode = False

    Range("A1").Select

    MsgBox "Copy / paste operation is complete.  Select OK to continue.", vbOKOnly + vbInformation, "Product Costing"

    Application.ScreenUpdating = True

End Sub

Answer 1

Heres a refactor of your code, adding the required offsets, and addressing a number of other issues:

• use correct data type for ans
• don't use Resume Next. What that says is I don't care if I had and error, just carry on regardless. Who knows whats going to happen next
• Don't use Activate or Select (unless you have a specific need to). Use Workbook, Worksheet, and Range objects instead. Note that Worksheets("Direct Materials") is implicity saying Activeworkbook.Worksheets("Direct Materials")
• you don't actually need to Copy/Paste for this. Use the Variant Array that .Value returns instead. This will be faster and not vulnerable to interuption by other apps using the clipboard. Also its a good habit to get into as it's usefull in all sorts of ways.

---

Sub copyCurrentToPrevious()
    Dim ans As VbMsgBoxResult
    Dim rng As Range

    On Error GoTo EH

    ans = MsgBox("Are you sure you want to copy Previous Month Variance to YTD Variance Tracking?  This action can not be undone." & vbNewLine _
        & vbNewLine & "Select Yes to proceed with the copy/paste operation or Select No to cancel.", vbYesNo + vbExclamation, "Product Costing")

    If ans = vbNo Then Exit Sub
    Application.ScreenUpdating = False

    With Worksheets("Direct Materials")
        Set rng = .Range("Z9:Z220")
        rng.Offset(0, Month(Now()) + 36).Value = rng.Value

        Set rng = .Range("Z226:Z306")
        rng.Offset(0, Month(Now()) + 36).Value = rng.Value

        Set rng = .Range("Z311:Z471")
        rng.Offset(0, Month(Now()) + 36).Value = rng.Value

        Set rng = .Range("Z476:Z524")
        rng.Offset(0, Month(Now()) + 36).Value = rng.Value
    End With

    MsgBox "Copy / paste operation is complete.  Select OK to continue.", vbOKOnly + vbInformation, "Product Costing"

    Application.ScreenUpdating = True
Exit Sub
EH:
    MsgBox "Something went horribly wrong!"
End Sub