VBA to copy cell range between 2 workbooks (1 Viewer)

TUSSFC · Nov 24, 2008

I've knocked together the code below and am at the point where I am getting no error messages but no value is being copied between the two workbooks/sheets.

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.

But the 2nd workbook definitely opens and closes correctly.

Any ideas?

Code:

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.Select
Range("A3").Select
Selection.Copy
Sheets("Tracker").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
xlBook.Close
End Sub

chergh · Nov 24, 2008

Try:

Code:

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

TUSSFC · Nov 24, 2008

Many thanks - worked perfectly!

ThaiByThai · Aug 12, 2009

Hello -

i did some search online and saw your post and i am having the same issue but the only different is i have multiple sheets that need to be update from a different workbook - is there a way to do that? i have 2 workbook and both workbook have 20 worksheets i need to be able to update workbook 2 from workbook1 but only selective column will be updated not all column in workbook1 will be updated in workbook2...

VBA to copy cell range between 2 workbooks (1 Viewer)

TUSSFC

Registered User.

chergh

blah

TUSSFC

Registered User.

ThaiByThai

Registered User.

Similar threads

Users who are viewing this thread