Excel VBA运行时错误1004；我在代码中没有选择

我正在尝试将一些输入复制到模型中，并将答案复制回第一张表。下面是代码。 “data_sheet”是具有许多输入的表，“Final Model”给出输出。非常简单；我正在尝试从中复制数据 将“data_sheet”更改为“最终模型”，然后将答案复制回来。当我尝试将答案复制回“data_sheet”时，出现错误 1004。仅第一部分效果就很好。

Private Sub CommandButton1_Click()


    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range
    Dim rngStart2 As Range, rngSource2 As Range, rngDest2 As Range

For i = 1 To 6

    Set wksSource = ActiveWorkbook.Sheets("data_sheet")
    Set wksDest = ActiveWorkbook.Sheets("Final Model")

    Set rngSource = wksSource.Range(Cells(5, 3 + i - 1), Cells(23, 3 + i - 1))
    'Paste Data Values
    Set rngDest = wksDest.Range("C14")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


'Till this code works. Now i just trying to reverse copy

    Set rngSource2 = wksDest.Range(Cells(38, 4), Cells(40, 4))
    Set rngDest2 = wksSource.Range(Cells(29, 3 + i - 1), Cells(31, 3 + i - 1))
    rngSource2.Copy
    rngDest2.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Next i

您收到错误的原因是因为您试图定义一个范围wksDest using Cells来自Active工作表，除非wksDest活跃。所以这个声明：

Set rngSource2 = wksDest.Range(Cells(38, 4), Cells(40, 4))

其实是这么说的：

Set rngSource2 = wksDest.Range(ActiveSheet.Cells(38, 4), ActiveSheet.Cells(40, 4))

由于无法使用另一张工作表上的单元格在一张工作表上定义范围（不采取其他特殊措施），为了避免这种情况，您可以使用With像这样阻止：

With wksDest
    Set rngSource2 = .Range(.Cells(38, 4), .Cells(40, 4))
End With
With wksSource
    Set rngDest2 = .Range(.Cells(29, 3 + i - 1), .Cells(31, 3 + i - 1))
End With

或者使用更直接的方法Resize method:

Set rngSource2 = wksDest.Cells(38,4).Resize(3)
Set rngDest2 = wksSource.Cells(29, 3 + i - 1).Resize(3)

上述两种方法都避免了不合格的情况Cells对象，这可能是 1004 错误的原因。

另外，由于您只是复制值，因此您可以进行简单的值分配，而不是copy and pastespecial，像这样：

rngDest2.Value = rngSource2.Value