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

Question

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...

Edit: I had tried this code but it only copies the value of D16 and copies it to the last row of column B..

Dim Datearray As Variant
N = Worksheet4.Cells(Rows.Count, "B").End(xlUp).Row + 1
Datearray = Worksheet3.Range("D16:D19")
Worksheet4.Cells(N, 2) = Datearray

Thanks in advance for the help!

Answer 1

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

EDIT

Dim n As Long
n = Worksheet4.Cells(Worksheet4.Rows.Count, "B").End(xlUp).Row + 1
Dim DateArray As Variant: DateArray = Worksheet3.Range("D16:D19").Value
Worksheet4.Cells(n, "B").Resize(UBound(DateArray, 1)).Value = DateArray

' or without the array:
Dim n As Long
n = Worksheet4.Cells(Worksheet4.Rows.Count, "B").End(xlUp).Row + 1
With Worksheet3.Range("D16:D19")
    Worksheet4.Cells(n, "B").Resize(.Rows.Count).Value = .Value
End With