Hi!
I have the below macro to copy data from a closed workbook at path C:\tmp\x.xlsx to the currently open workbook:
Code
Sub GetDataFromClosedBook()
Dim mydata As String
mydata = "='C:\tmp\[x.xlsx]Sheet1'!$a$1:$g$2501" 'data location & range to copy
With ThisWorkbook.Worksheets("chk").Range("a1:g2501") 'link to currently open workbook/ worksheet
.Formula = mydata
.Value = .Value 'convert formula to text
.WrapText = False
End With
End Sub
The code works perfectly if I was copying the data into the range A1:G2501 of the currently open workbook.
However, if I wish to copy the data to some other range such as C1:I2501 of the currently open workbook (with source data range being A1:G2501), col. A & B remain blank, columns C to G get the same data as in col. C to G of the source workbook while I get #Value in Col. H & I.
Can some one point out what am I missing here.
PS: Please note that the source workbook should not be opened while copying data since it will slow down the process a lot.
Thanx,
Naira