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;
Note that this code will copy the range of cells specified and will copy only values from the source to destination sheet.
If you want to copy from other sheets you'll have to only replicate the following code specifying the new range and the new Source's sheet:
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

Enjoy. :-)

SOURCE

LINK

LANGUAGE
ENGLISH