求助：同工作表内循环执行间隔两行的复制粘贴VBA实现

Got it, let's get this sorted for you. Below is a custom VBA macro that will handle copying your B48:B52 range, pasting it starting at B55:B59, then repeating the process with a 2-row gap each time—all 1100 times you need.

The VBA Code #

Sub RepeatPasteWithGap()
    Dim sourceRange As Range
    Dim targetStartRow As Integer
    Dim i As Integer
    
    ' Set the source range we want to copy (B48:B52)
    Set sourceRange = ThisWorkbook.ActiveSheet.Range("B48:B52")
    
    ' First paste starts at row 55 (B55:B59)
    targetStartRow = 55
    
    ' Loop 1100 times to repeat the paste operation
    For i = 1 To 1100
        ' Define the target range for this iteration (5 rows long)
        Dim targetRange As Range
        Set targetRange = ThisWorkbook.ActiveSheet.Range("B" & targetStartRow & ":B" & (targetStartRow + 4))
        
        ' Copy values only (swap to xlPasteAll if you need formatting/formulas)
        sourceRange.Copy
        targetRange.PasteSpecial Paste:=xlPasteValues
        
        ' Move to the next target start: 5 rows of data + 2 blank rows = 7 rows offset
        targetStartRow = targetStartRow + 7
    Next i
    
    ' Clear the clipboard to avoid leftover copy selection
    Application.CutCopyMode = False
    MsgBox "Pasting finished! 1100 iterations completed.", vbInformation
End Sub

How to Use This Macro #

1. Open your Excel workbook with the data you want to copy.
2. Press Alt + F11 to open the VBA Editor.
3. In the left-hand Project Explorer, right-click your workbook name > Insert > Module.
4. Paste the code above into the blank module that opens.
5. Press F5 to run the macro, or switch back to Excel, go to the Developer tab > Macros, select RepeatPasteWithGap, and click Run.

Key Details to Note #

• Paste Type: The code uses xlPasteValues to only copy cell values. If you need to copy formatting, formulas, or other cell properties, replace xlPasteValues with xlPasteAll. Alternatively, you can simplify that line to sourceRange.Copy targetRange (this copies everything by default).
• Specific Sheet: If you're working with a sheet that's not the active one, replace ActiveSheet with your sheet name (e.g., ThisWorkbook.Sheets("DataSheet").Range(...)) to avoid mistakes.
• Performance: For 1100 iterations, this should run quickly, but if you want to speed it up even more, you can add Application.ScreenUpdating = False at the start of the macro and Application.ScreenUpdating = True at the end (this turns off Excel's screen refresh while the macro runs).

内容的提问来源于stack exchange，提问作者MKM