I have the below macro to copy data from a closed workbook at path C:\tmp\x.xlsx to the currently open workbook:
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.