vba to Extract data from a .xlsm file to another without opening source file

[Rodrigues]

All
I'm using a piece of vba code file attached to extract data from a .xlsm file to another without opening the source file, the problem is just copies column A values from source file and past them into the wrong rows on the destination files.

Source file (Production.xlsm), worksheet 1 called data, which gets data from a userform and it's set to very hidden; array will be A3 to AR (count all rows as it is filled in).

Destination file (MasterData.xlsm), the data needs to go on Sheet3, from cell A2
Any help will be appreciated.
Thanks R

 

[Chihiro]

I'm using a piece of vba code file attached to extract data from a .xlsm file to another without opening the source file,

But your code opens the source file...

Set src = Workbooks.Open("C:\Production.xlsm", True, True)

You'd need to use different method (such as ADODB), if you don't want to open the source workbook.

At any rate issue is that you have "A2" & iCnt in your code... so starts from row A21.

    Dim iCnt As Integer
    For iCnt = 1 To iTotalRows
        Worksheets("Sheet3").Range("A2" & iCnt).Formula = src.Worksheets("Sheet1").Range("A" & iCnt).Formula
    Next iCnt

Why ".Formula"? From your description it sounds like it's simple value instead of formula that's contained in the range specified in the source.

Instead of using loop, just copy the range. Something like...

src.Worksheets("Sheet1").Range("A3:A" & iTotalRows).Copy Worksheets("Sheet3").Range("A2")

 

[vletm]

Yes, these are these read from CLOSED file cases ...
This won't open 'Production.xlsm'-file!!
This is another sample ...
Note1: Production.xlsm, There HAVE TO BE that YELLOW-cell with formula!
Note2: MasterData.xlsm, There HAVE TO BE those YELLOW-cells
Note3: MasterData.xlsm, after the 1st opening You should update link
>> Press [UpDate]-button
>>> Questions?

 

[Rodrigues]

Thanks All. I will have a go.
Much appreciated.
R