通过Excel VBA复制粘贴单元格

Question

我写了一个代码，复制Sheet1单元格A2和B2，然后将这些单元格粘贴到Sheet2单元格G5和G6中。

我一直在尝试创建一个循环，在第一种情况下，当我再次运行代码时，它应该复制细胞A3 and B3，然后粘贴到Cells G5 and G6 (these cell will always be same)。

我想在每次运行宏时向前移动一行。

我已经尝试了OFFSET函数，但它只工作一次。

Sub copy()
Dim lRow, i As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = Sheet1
Set sht2 = Sheet2
sht1.Cells(2, 1).Offset(1, i).Copy Destination:=sht2.Cells(5, 7)
sht1.Cells(2, 2).Offset(1, i).Copy Destination:=sht2.Cells(6, 7)
End Sub

Answer 1

每次运行时，在输入表中找到前一个值并复制下一个值

option explicit
Sub copy()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = Sheet1
Set sht2 = Sheet2

dim r as range
set r = range(sht1.Cells(2, 1), sht1.Cells(2, 1).end(xlDown))
dim offset_row as variant
if isempty(sht2.Cells(5, 7).value) then
offset_row=0
else
offset_row=application.worksheetfunction.match(sht2.Cells(5, 7).value, r, 0)
endif
if not iserror(offset_row) then
if offset_row <> r.rows.count then
sht1.Cells(2, 1).offset(offset_row, 0).Copy Destination:=sht2.Cells(5, 7)
sht1.Cells(2, 1).offset(offset_row, 1).Copy Destination:=sht2.Cells(6, 7)
endif
endif

End Sub