时间戳

首页 > 解决方案 > 将数据从多张纸复制到一张纸中

excel - 将数据从多张纸复制到一张纸中

问题描述

我正在尝试将特定单元格（I106 到 I160）从多张纸复制到第一张纸中。每个都应在新列中彼此相邻复制。但是，它将数据复制到彼此下方的 1 列中。

提前致谢

Sub CopyIt()

    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.name <> "All Data" Then
            ws.Range("I106:I160").Copy
            Sheets("All Data").Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next
    Application.ScreenUpdating = True
End Sub

标签： excelvba

解决方案

尝试这个：

Sub CopyIt()

    Dim ws As Worksheet, c As Range, wsData As Worksheet, wb As WorkBook
    Set wb = ActiveWorkbook
    Set wsData = wb.Sheets("All Data")
    Application.ScreenUpdating = False
    Set c = wsData.Cells(Rows.Count, "B").End(xlUp).Offset(1) 'start here
    For Each ws In wb.Worksheets
        If ws.name <> wsData.Name Then
            With ws.Range("I106:I160")
                c.Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
            Set c = c.offset(0, 1)'next column 
        End If
    Next
    Application.ScreenUpdating = True
End Sub

推荐阅读