Excel – VBA to copy cell range between 2 Workbooks

SCENARIO

The source sheet is on another workbook.

The destination sheet is in the same workbook that the code is being run from - so I am opening another file and copying the data from there. I suspect the problem is that it is not copying the data correctly.

SOLUTION

Sub Import_Tracker()
Dim myPath As String
myPath = Application.GetOpenFilename
If myPath = "False" Then Exit Sub
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlThisSheet As Excel.Worksheet
Set xlBook = Workbooks.Open(myPath)
Set xlSheet = xlBook.Worksheets("Tracker")
Set xlThisSheet = ThisWorkbook.Worksheets("Tracker")
xlSheet.Range("A3").Copy
xlThisSheet.Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
xlBook.Close
End Sub

NOTE

• Application.GetOpenFilename - it will open a dialog box in which the user will choice the Excel document to open in backgroung;
• ThisWorkbook.Worksheets - it will open the sheet from the Source file from which data will be copied;

Set xlSheet = xlBook.Worksheets("Tracker")
Set xlThisSheet = ThisWorkbook.Worksheets("Tracker")
xlSheet.Range("A3").Copy
xlThisSheet.Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False