vba 每次启动宏时如何复制列并将值粘贴到新列

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

提问by Avatar Coder

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.

我有一个计算 Z 列差异的电子表格。在月底，我想将这些值复制并粘贴到同一电子表格中的另一列中，以跟踪每月的差异。

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

我有一个宏可以从 Z 列复制到 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:

我希望每次运行宏时，复制 Z 列中的值并将其粘贴到使用以下计划的新列中：

• 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

• 第 1 个月 = 值应粘贴在 BK 列中
• 第 2 个月 = 值应粘贴在列 BL 中
• 第 3 个月 = 值应粘贴在列 BM 中
• 第 4 个月 = 值应粘贴在列 BN 中
• 第 5 个月 = 值应粘贴在 BO 列中
• 第 6 个月 = 值应粘贴在 BP 列中
• 第 7 个月 = 值应粘贴在列 BQ 中
• 第 8 个月 = 值应粘贴在列 BR 中
• 第 9 个月 = 值应粘贴在列 BS 中
• 第 10 个月 = 值应粘贴在列 BT 中
• 第 11 个月 = 值应粘贴在列 BU 中
• 第 12 个月 = 值应粘贴在 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?

在第 12 次迭代之后，我希望将 Z 列中的值复制到 BK 列（起点）中。我相信这可以使用循环来完成？

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

回答by chris neilsen

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 Activateor Select(unless you have a specific need to). Use Workbook, Worksheet, and Rangeobjects instead. Note that Worksheets("Direct Materials")is implicity saying Activeworkbook.Worksheets("Direct Materials")
• you don't actually need to Copy/Pastefor this. Use the Variant Arraythat .Valuereturns 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.

• 使用正确的数据类型 ans
• 不要使用Resume Next. 那就是说我不在乎我是否有错误，不管怎样都继续。谁知道接下来会发生什么
• 不要使用Activate或Select（除非您有特殊需要）。请改用Workbook、Worksheet和Range对象。请注意，这Worksheets("Direct Materials")是隐含的说法Activeworkbook.Worksheets("Direct Materials")
• 你实际上不需要Copy/Paste为此。使用Variant Array该.Value回报率来代替。这将更快，并且不容易受到使用剪贴板的其他应用程序的中断。这也是一个很好的习惯，因为它在各种方面都很有用。

---

---

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