vba - Excel VBA复制粘贴错误

Question

我有两张相同的数据

A  B  C
   5  6
4  3  3 

一级方程式

Sub Button1_Click()

Dim Current As Worksheet


  Range("A2").Copy _
  Destination:=Range("A1")
  Range("A2").ClearContents         

End Sub

这个公式对我有用。但我需要将此脚本应用于所有工作表，

公式 2

Dim Current As Worksheet

         ' Loop through all of the worksheets in the active workbook.
         For Each Current In ThisWorkbook.Worksheets
    With Current



           Range("A2").Copy _ Destination:=Range("A1")
  Range("A2").ClearContents
            End With
         Next Current


End Sub

--> 它有效，但 A1 中的值也被删除。它并没有被用于所有工作表。只有事件的工作表。

Answer 1

一个 With ... End With statement可以在命令 block 中携带父工作表引用，但您必须为每个 .Range 添加前缀或 .Cells引用句点(又名句号)来接受父工作表关系。

Dim Current As Worksheet

' Loop through all of the worksheets in the active workbook.
For Each Current In ThisWorkbook.Worksheets
    With Current
       .Range("A2").Copy Destination:=.Range("A1")
       .Range("A2").ClearContents
    End With
Next Current

备注 .Range而不是 Range .