Selected Answer
The code below will read data from a workbook and transfer the values found in a specified range of cells to a similarly specified range in the workbook that contains the code. Paste the code in a standard code module (by default 'Module1'). Set the values of the four constants at the top of the code to those that you require in your own environment.
Option Explicit
Sub ReadData()
' 21 Dec 2017
Const SourceSheet As String = "Credit Note"
Const SourceRange As String = "A1:H14"
Const TargetSheet As String = "IS"
Const TargetRange As String = "A1"
Dim WbS As Workbook ' Source workbook
Dim Ffn As Variant ' full file name (with path)
Dim Arr As Variant
Dim Rng As Range
Ffn = Application.GetOpenFilename("Excel-files,*.xls*", _
1, "Select the source workbook", , False)
If VarType(Ffn) = vbBoolean Then Exit Sub ' user cancelled
Application.ScreenUpdating = False
Workbooks.Open Ffn, ReadOnly:=True
Set WbS = ActiveWorkbook ' set Source workbook
On Error Resume Next
Arr = WbS.Worksheets(SourceSheet).Range(SourceRange).Value
If Err Then
MsgBox "An error occurred reading data from the source." & vbCr & _
"The likely reason is that the specified worksheet" & vbCr & _
"doesn't exist in that workbook.", _
vbCritical, "Data could not be retrieved"
End If
On Error GoTo 0
' paste data to this workbook
Set Rng = ThisWorkbook.Worksheets(TargetSheet).Range(TargetRange)
Rng.Resize(UBound(Arr), UBound(Arr, 2)).Value = Arr
WbS.Close xlDoNotSaveChanges
Application.ScreenUpdating = True
End Sub
The code uses Window's own file picker to let you pick the source workbook. The destination workbook is the one containing the code. You can easily change that yourself.