使用VBA在Excel中自动复制行并粘贴为列

Sub CopyRowsToColumns()
    Dim sourceRange As Range
    Dim targetRange As Range
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim i As Long
    
    ' 设置源数据范围，这里假设源数据在Sheet1的A1:E10
    Set sourceRange = ThisWorkbook.Sheets("Sheet1").Range("A1:E10")
    
    ' 获取源数据的最后一行和最后一列
    lastRow = sourceRange.Rows.Count
    lastColumn = sourceRange.Columns.Count
    
    ' 设置目标数据的起始位置，这里假设目标数据从Sheet2的A1开始
    Set targetRange = ThisWorkbook.Sheets("Sheet2").Range("A1")
    
    ' 循环复制行并粘贴为列
    For i = 1 To lastRow
        sourceRange.Rows(i).Copy
        targetRange.Offset(0, i - 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Next i
    
    ' 清除剪贴板内容
    Application.CutCopyMode = False
End Sub