Copying data from external workbook into current workbook based on cell value

Hi,

I've got a table of data which has to be refreshed from data held externally in another workbook.

The key steps would be;

1. The source financial data is held externally at an address that can change and I want to be able to state its location as a variable in the current workbook.

2. The data to be refreshed would exist in a specified range both in the current and external workbooks.

3. The process of refreshing data would use the cell value in a column of the range, one row at a time.

4. With each matching current workbook cell value, the corresponding row would be found in the external workbook and two values would be copied from specified columns on that row into specified columns in the current workbook.

I haven't got a clue how to start this and my research in the forum hasn't really helped because of my level of VBA experience.

Any help is much appreciated.

Thanks,

Louise

Hi there,

I've got a minute to spare and would like to help you. I don't know about others, but it would help me to get a working code for you, if you'd attach both workbooks (with some indication what goes where etc.).

Thanks

Public Sub copyto()

    Dim cell As Range, wb As Workbook, twb As Workbook, dwb As Workbook
    Dim nname As String, lwf As String, dwf As String

    Application.Screenupdating = false

    Set twb = ThisWorkbook
    Set dwb = Workbooks.Open(Range("B2"))

    twb.Activate

    For Each cell In Range("A5", Cells(Rows.Count, 1).End(xlUp))
        If cell.Value <> "" Then
            lwf = cell.Value

            dwb.Activate

            With twb
                cell.Offset(0, 1) = Cells.Find(lwf, after:=Cells(1, 1), lookat:=xlWhole, Searchdirection:=xlNext, Searchorder:=xlByRows).Offset(0, 4)
                cell.Offset(0, 2) = Cells.Find(lwf, after:=Cells(1, 1), lookat:=xlWhole, Searchdirection:=xlNext, Searchorder:=xlByRows).Offset(0, 8)

            End With

        End If

    Next cell

    twb.Activate
    dwb.Close savechanges = False
    Application.screenupdating = true

End Sub

Please let me know if this does the job.

Does this imply that there is not always a match and the code has returned an error for you? Because I don't see a problem if there are more rows in "financial data" as long as the searched cell value is among them (or I'm missing something here).