基于带偏移量的单元格值复制循环

Question

我对VBA和编码还很陌生，但我正在尝试创建一个直到循环，该循环可以看到特定单元格中的值，然后复制并粘贴不同工作表的行，次数与指定单元格值相同，偏移量为24行。代码正在循环，但我无法使其偏移，但由于我的知识有限，我不确定哪里出了问题，或者这是否是正确的方法。

Sub Loop_one()
Dim ws As Worksheet, wsInput As Worksheet, wsOutput As Worksheet
Dim i As Byte, j As Long, OffsetBy As Long
Dim cell As Range, lngDataRows As Long
i = Sheet2.Range("D23").Value
j = 1
Set ws = Sheets("CFS")
Set wsInput = Sheets("Table")
Set wsOutput = Sheets("RD")

wsInput.Visible = xlSheetVisible
ws.Activate
If Range("D20") = ("1") And Range("D22") = ("1") Then
wsOutput.Select
wsInput.Range("B2:K28").copy wsOutput.Range("B14")
Do Until j = i
OffsetBy = 1
j = j + 1
wsOutput.Range("B14").Offset(lngDataRows + OffsetBy, 0).Select
OffsetBy = OffsetBy + 23
wsInput.Range("B2:K28").copy
wsOutput.Range("B14").PasteSpecial Paste:=xlPasteColumnWidths, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Loop
wsOutput.Select
End If
End Sub

Answer 1

使用Range变量作为粘贴目标更容易，因此可以避免所有这些偏移计算。。。

像这样应该工作：

Sub Loop_one()
Dim ws As Worksheet, wsInput As Worksheet, wsOutput As Worksheet
Dim reps As Long, j As Long, OffsetBy As Long
Dim pastePos As Range, lngDataRows As Long

Set ws = Sheets("CFS")
Set wsInput = Sheets("Table")
Set wsOutput = Sheets("RD")

reps = Sheet2.Range("D23").Value 'is this one of the 3 sheets above?
wsInput.Visible = xlSheetVisible

If ws.Range("D20") = 1 And ws.Range("D22") = 1 Then
Set pastePos = wsOutput.Range("B14") ' first paste destination
For j = 1 To reps
wsInput.Range("B2:K28").Copy pastePos
Set pastePos = pastePos.Offset(24) '  move paste destination 24 rows down
Next j
End If

End Sub