excel VBA循环将数据从第n列复制并粘贴到另一个工作簿

y3bcpkx1 于 2023-03-31 发布在其他

关注(0)|答案(1)|浏览(164)

我尝试创建一个循环，从Workbook1.Worksheet1的M列中获取数据，从第9行开始到第700行，然后将其粘贴到Workbook2.Worksheet2的F8单元格中。之后，它将从Workbook1中获取下一行（第N列），并将其粘贴到Workbook2 8列后（第N8单元格）。它重复24次以获取Workbook1中的所有行。

Sub importdata()

Dim J As Integer
Dim K As Integer
J = 6
K = 13
Wrksht1 As Range
Wrksht2 As Range
    Set wb1 = Workbooks("Workbook1.xlsm")
    Set wb2 = Workbooks("Workbook2.xlsb")

    Set ws1 = wb1.Sheets("Worksheet1")
    Set ws2 = wb2.Sheets("Worksheet2")

For I = 1 To 24
    Set Wrksht2 = ws2.Range(K & 9)
    Set Wrksht1 = ws1.Range(J & 8)
        Wrksht1 = Wrksht2
        J = J + 8
        K = K + 1
Next I

End Sub

这是我现在的代码，我让循环工作，所以它应该把值放在workbook2的正确位置，但我不能让循环从workbook1的行中抓取值并将值粘贴到定向位置。

excel

来源：https://stackoverflow.com/questions/75870340/vba-loop-to-copy-and-paste-data-from-nth-column-to-another-workbook

1条答案

按热度按时间

b91juud31#

尝试

Sub CopyRowsToDifferentColumns()
    
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet
    Dim sourceRange As Range
    
    Dim destWB As Workbook
    Dim destWS As Worksheet
    Dim destRange As Range
    
    Dim sourceCols As Long
    Dim destCols As Long
    Dim i As Long
    
    ' Set source workbook, worksheet, and range
    Set sourceWB = Workbooks("Workbook1.xlsx")
    Set sourceWS = sourceWB.Worksheets("Worksheet1")
    Set sourceRange = sourceWS.Range("M9:AJ700") '24 columns
    sourceCols = sourceRange.Columns.Count

    ' Set destination workbook, worksheet, and range
    Set destWB = Workbooks("Workbook2.xlsb")
    Set destWS = destWB.Worksheets("Worksheet2")
    Set destRange = destWS.Range("F8")
    destCols = sourceCols * 8
    
    ' Loop through each column of the source range and paste it in a different column in the destination range
    For i = 1 To sourceCols
        sourceRange.Columns(i).Copy
        destRange.Offset(0, (i - 1) * 8).PasteSpecial xlPasteValues
    Next i
    
    ' Clear clipboard
    Application.CutCopyMode = False
    
End Sub

赞(0）回复(0）举报 2023-03-31

我来回答

excel VBA循环将数据从第n列复制并粘贴到另一个工作簿

1条答案

相关问题

热门标签

最新问答