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:

    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

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


    Hi naira,


    Try this:

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

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