请求开发Excel宏：按指定行数批量复制选定列数据

Got it, I’ve put together a VBA macro that does exactly what you need: it copies chunks of 1000 rows from column K, and handles the remaining 500 rows in the final pass—no need to paste anywhere, just the copy action itself.

Here’s the code:

Sub BatchCopyColumnRows()
    Dim ws As Worksheet
    Dim totalRows As Long
    Dim copySize As Long
    Dim startRow As Long
    Dim endRow As Long
    
    ' Set your parameters here
    Set ws = ThisWorkbook.ActiveSheet ' Use specific sheet if needed, e.g., Sheets("Sheet1")
    copySize = 1000 ' Number of rows to copy per batch
    startRow = 1 ' Starting row of your data (adjust if you have a header row, e.g., start at 2)
    
    ' Get total number of rows with data in column K
    totalRows = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
    
    ' Loop through each batch
    Do While startRow <= totalRows
        ' Calculate end row for current batch
        endRow = startRow + copySize - 1
        ' If end row exceeds total rows, set it to total rows
        If endRow > totalRows Then
            endRow = totalRows
        End If
        
        ' Copy the range from K[startRow] to K[endRow]
        ws.Range("K" & startRow & ":K" & endRow).Copy
        
        ' Uncomment the line below if you want a short delay between copies (for verification)
        ' Application.Wait Now + TimeValue("00:00:01")
        
        ' Update start row for next batch
        startRow = endRow + 1
    Loop
    
    MsgBox "Batch copying completed!", vbInformation
End Sub

How this works: #

• Adjustable Parameters: Tweak copySize if you ever need a different batch size, startRow (set to 2 if your column has a header row), and ws to target a specific sheet instead of the active one.
• Auto Row Count: It automatically detects the last row with data in column K, so you don’t have to hardcode 10500—this works even if your row count changes later.
• Batch Logic: The loop runs until all rows are processed. For each iteration, it calculates the end of the current batch, copies that range, then moves to the next set of rows.
• Final Batch Handling: If remaining rows are less than 1000 (like your 500), it adjusts the end row to the total number of rows to copy only what’s left.

To use this macro: #

1. Open your Excel file.
2. Press Alt + F11 to open the VBA Editor.
3. Right-click your workbook in the Project Explorer > Insert > Module.
4. Paste the code above into the module.
5. Adjust parameters like startRow if needed.
6. Press F5 to run the macro, or assign it to a button in Excel for easier access later.

Let me know if you need to tweak anything—this should handle your 10500-row column perfectly!

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