且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

使用VBA将值从一个表粘贴到另一个表

更新时间:2022-12-11 23:21:53

只需一次复制所有数据,而不必一次执行一行.

Just copy the data all at once, no need to do it a row at a time.

Sub CopyData()

    With ThisWorkbook.Sheets("Tabled data")
        Dim sourceRange As Range
        Set sourceRange = .Range(.Cells(2, 1), .Cells(getLastRow(.Range("A1").Parent), 14))
    End With

    With ThisWorkbook.Sheets("Running list")
        Dim pasteRow As Long
        Dim pasteRange As Range
        pasteRow = getLastRow(.Range("A1").Parent) + 1
        Set pasteRange = .Range(.Cells(pasteRow, 1), .Cells(pasteRow + sourceRange.Rows.Count, 14))
    End With

    pasteRange.Value = sourceRange.Value

End Sub
Function getLastRow(ws As Worksheet, Optional colNum As Long = 1) As Long

    getLastRow = ws.Cells(ws.Rows.Count, colNum).End(xlUp).Row

End Function