Copy values of Sheet1 to End of Column of Sheet2 using Array-CodePudding

I am a beginner with VBA so am not yet used to arrays.

I have a list of values in Sheet1 in Range("D16:D19"), which need to be copied to the last row of column "B" of Sheet2. I would like to use an array as currently my code only copies the value of D16 to the last row...

Thanks in advance for the help!

CodePudding user response：

Dim arr as variant
arr = sheet1.Range("D16:D19")
Sheet2.Range("B16:B19") = arr

CodePudding user response：

Copy a Range

Option Explicit

Sub CopyRange()
    ' Source
    Const sName As String = "Sheet1" ' read from
    Const sRangeAddress As String = "D16:D19"
    ' Destination
    Const dName As String = "Sheet2" ' written to
    Const dCol As String = "B"
    ' Both
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range(sRangeAddress)

    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dfCell As Range
    Set dfCell = dws.Cells(dws.Rows.Count, dCol).End(xlUp).Offset(1)
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    ' You can omit ', srg.Columns.Count' because your copying a one-column range.
    
    ' Copy (there is no need for an array, 'srg.Value' is already one).
    drg.Value = srg.Value

End Sub