VBA to open file, copy data then close file

[Zunit]

Hi,

I need to extract data from 130 files containing data is exactly in the same format.

I have created a file into which the data must be extracted to.

In this file:
Sheet name: Sheet1
Cell C7 = path to find the data file to open and copy info from (C:\Users\me\Dropbox\Consolidation test)
C8 = file name to open at the above path (e.g. [TRACK.xlsm])
C9 = worksheet name in the file (e.g. accounts pack)
A11 = Description to look for (e.g. Trading profit)
B11 = Column letter for the column where to look for the description (e.g. A)
C5 = Column letter for the column where the answer should come from (e.g. H)

In above case say the result comes from cell H256.

Using the above variables, I want to open a file, copy the value in H256 to Cell C11 (in the first file) and then close the file [TRACK.xlsm].

 

[Nine Zero]

Hey!

Try this out

Sub DataCopy()
Dim filePath As String
Dim mR, cR As Range
Dim masterBook, otherBook As Workbook
'Setup your 2 workbooks
Set masterBook = ActiveWorkbook
With masterBook.Sheets("Sheet1")
filePath = .Range("C7").Value & "\" & .Range("C8").Value
End With
Set otherBook = Workbooks.Open(filePath)
'Setup your 2 ranges
Set mR = masterBook.Sheets("Sheet1").Range("C11")
Set cR = otherBook.Sheets("accounts pack").Range("H256")
'Set the range in your masterbook equal to the range in your otherbook
mR.Value = cR.Value
'Close your otherbook without saving anything
otherBook.Close False
End Sub

 

[Nine Zero]

The above will take the value of cell H256 from the file path you type in the cells C7 and C8 in your masterbook and "copy" it over to cell C11 then it will close the otherbook and not save anything

 

[Zunit]

Hi
Thanks for this. This is working quite well, except for the H256 part.
The code needs to know to look in column A in the second worksheet for the value in cell A11 in the first workbook, then return the corresponding value from column H (Cell H256 was given as an example of what the cell could be).

 

[Nine Zero]

Ahh i see alright so in A11 is the reference to what too look for and in B11 is the column

try this now

Sub DataCopy()
Dim filePath, refStr, col, colLook, val As String
Dim mR, cR, r As Range
Dim masterBook, otherBook As Workbook
'Setup your 2 workbooks
Set masterBook = ActiveWorkbook
With masterBook.Sheets("Sheet1")
filePath = .Range("C7").Value & "\" & .Range("C8").Value
refStr = .Range("A11").Value
col = .Range("B11").Value
End With
colLook = "" & col & ":" & col & ""
Set otherBook = Workbooks.Open(filePath)
'Setup your 2 ranges
Set mR = masterBook.Sheets("Sheet1").Range("C11")
With otherBook.Sheets("accounts pack")
    For Each r In Range(colLook)
        If r.Value = refStr Then
            val = r.Offset(, 7).Value
        End If
    Next r
End With
'Set the range in your masterbook equal to the range in your otherbook
mR.Value = val
'Close your otherbook without saving anything
otherBook.Close False
End Sub