Excel VBA行列索引需求：批量跨工作表复制指定行数据

Hey there! Let's get this VBA macro sorted for you. You’ve already nailed the single-column copy logic, and now you just need to scale that up to loop through all your source columns. Let’s break this down step by step.

First, let's align on the core goal: you want to loop through every column in your source sheet, grab rows 1-5 of each column, and drop that data into the corresponding column (rows 1-5) in your target sheet. Here's how to turn your one-off code into a scalable macro:

Full Macro for Column-by-Column Copy #

Sub BatchCopyColumns()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastUsedCol As Long
    Dim currentCol As Long
    
    ' Set up references to your sheets (update names if needed!)
    Set sourceSheet = ThisWorkbook.Worksheets("Temp")
    Set targetSheet = ThisWorkbook.Worksheets("Info")
    
    ' Find the last column with data in the source sheet
    lastUsedCol = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    
    ' Loop through each column in the source sheet
    For currentCol = 1 To lastUsedCol
        ' Directly transfer values (*faster than copy/paste!*)
        targetSheet.Range(targetSheet.Cells(1, currentCol), targetSheet.Cells(5, currentCol)).Value = _
            sourceSheet.Range(sourceSheet.Cells(1, currentCol), sourceSheet.Cells(5, currentCol)).Value
    Next currentCol
    
    ' Optional: Pop up a confirmation when done
    MsgBox "All columns copied successfully!", vbInformation
End Sub

Wait—your existing code looks a bit different... #

Looking at your example lines, you're copying non-contiguous row blocks from a single source column (HK) to multiple target columns (B, C, etc.). If your actual data is structured as 4-row blocks with a 1-row gap in one source column (instead of data spread across multiple columns), here's the adjusted macro to handle that scenario:

Sub BatchCopyRowBlocks()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceRowStart As Long
    Dim targetCol As Long
    
    Set sourceSheet = ThisWorkbook.Worksheets("Temp")
    Set targetSheet = ThisWorkbook.Worksheets("Info")
    
    ' Start with the first data block (matches your HK5:HK8 example)
    sourceRowStart = 5
    ' Start pasting at target column B (column index 2)
    targetCol = 2
    
    ' Keep looping until we hit an empty row in the source column HK
    Do While sourceSheet.Cells(sourceRowStart, "HK").Value <> ""
        ' Copy 4 rows from source to current target column
        targetSheet.Range(targetSheet.Cells(3, targetCol), targetSheet.Cells(6, targetCol)).Value = _
            sourceSheet.Range(sourceSheet.Cells(sourceRowStart, "HK"), sourceSheet.Cells(sourceRowStart + 3, "HK")).Value
        
        ' Jump to the next data block (skip 1 gap row)
        sourceRowStart = sourceRowStart + 5 ' 4 data rows + 1 gap row
        ' Move to the next target column
        targetCol = targetCol + 1
    Loop
    
    MsgBox "All data blocks copied successfully!", vbInformation
End Sub

Quick Tips to Make This Better: #

• Use .Value instead of copy/paste: This is way faster and avoids clipboard conflicts—it just transfers values directly from one range to another.
• Define sheet references: Setting sourceSheet and targetSheet makes your code easier to read and update later (no need to hunt through lines if you rename a sheet).
• Dynamic ranges: Both macros adapt automatically—first one finds the last used column, second one stops when it hits empty data, so you don’t have to hardcode ranges every time.

Just pick the macro that matches your actual data setup: the first if your data is spread across multiple source columns, the second if it’s in blocks within a single column like your example shows.

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