VBA: Copiar Range de outro Workbook

Se pretendermos copiar um Range de uma Worksheet de outro Workbook, Range esse que deve terminar na linha anterior à 1ª linha vazia existente, podemos utilizar o seguinte código:

Sub Insere()
‘—————————————————————————————
‘ Procedimento : Insere
‘ Autor : JORGEROD
‘ Data : 15-11-2018
‘ Propósito : Copia os valores de final do dia, provenientes de outro Workbook
‘—————————————————————————————
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom1 As Worksheet
Dim vNumber As Variant
Dim vNumber1 As Variant
Dim N As Long

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

‘————————————————————-
‘Open file with data to be copied

vFile = Application.GetOpenFilename(“Excel Files (*.xl*),” & _
“*.xl*”, 1, “Select Excel File”, “Open”, False)

If TypeName(vFile) = “Boolean” Then

Exit Sub
Else

Set wbCopyFrom = Workbooks.Open(vFile)

Set wsCopyFrom1 = wbCopyFrom.Worksheets(1)
End If

‘————————————————————–
N = Cells(5, 3).End(xlDown).Row
Range(“C5:I” & N).Select

vNumber1 = wsCopyFrom1.Range(“C5:I” & N).Value
wsCopyTo.Range(“Valor_Actual”).Value = vNumber1

ActiveWorkbook.Close Savechanges:=False

End Sub