fine tune macro to copy data from closed workbook

  • Hi!

    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.



  • Re: fine tune macro to copy data from closed workbook

    Hi naira,

    Try this:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!