title: How to Copy Column and Paste Values to a New Column Every Time Macro Is Initiated
body: <p>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.</p>
  
  <p>I have a macro to copy from column Z to column BK.</p>
  
  <p>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: </p>
  
  <ul>
  <li>Month 1 = Values Should be Pasted in Column BK </li>
  <li>Month 2 = Values Should be Pasted in Column BL</li>
  <li>Month 3 = Values Should be Pasted in Column BM</li>
  <li>Month 4 = Values Should be Pasted in Column BN</li>
  <li>Month 5 = Values Should be Pasted in Column BO</li>
  <li>Month 6 = Values Should be Pasted in Column BP</li>
  <li>Month 7 = Values Should be Pasted in Column BQ</li>
  <li>Month 8 = Values Should be Pasted in Column BR</li>
  <li>Month 9 = Values Should be Pasted in Column BS</li>
  <li>Month 10 = Values Should be Pasted in Column BT</li>
  <li>Month 11 = Values Should be Pasted in Column BU</li>
  <li>Month 12 = Values Should be Pasted in Column BV</li>
  </ul>
  
  <p>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?</p>
  
  <p>I am having a difficult time coming up with the loop logic/coding.</p>
  
  <pre><code>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." &amp; vbNewLine _
        &amp; vbNewLine &amp; "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
  </code></pre>