Update a target workbook with values from a source workbook

[SpellBound]

Hi,

I have attached 2 example workbooks (TargetBook and SourceBook) each contains a sheet (TargetSheet and SourceSheet). I need to lookup data in the SourceSheet, and where the Group matches, the Quantity is added to the relevent Group in the TargetSheet. I can do this with a VLookup, but have no idea where to start with VBA. Can anyone help me as it has to be done using VBA?

If you look at the attached spreadsheet examples it will make more sense.

[SpellBound]

Hi,

I think I might be getting close to my solution. I have adapted some code I found which I placed in my TargetBook. However, I am not sure how to point it to the columns I want it to look up. When I run my code as is, it does something (not sure what) but I don't get the results I need. I think my problem is in the VLookup line. Can anyone help? Or suggest a more efficient way to update one workbook with values from another? Here is my code so far:

Sub myTest()

SourceBook = "C:\TestCheck\SourceBook.xls"
Set wkb0 = Workbooks.Open(SourceBook)
Set rng = wkb0.Sheets("SourceSheet").Columns("B:B")
TargetBook = "C:\TestCheck\TargetBook.xls"
Set wkb1 = Workbooks.Open(TargetBook)
With TargetBook.Sheets("TargetSheet")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lRow
.Cells(i, 23) = Application.VLookup((.Cells(i, 3)), rng, 4, False)
Next i
End With

End Sub

[klen_]

Dim RowMax As Long
With ThisWorkbook.Worksheets("TargetSheet")

    RowMax = .UsedRange.Rows.Count + .UsedRange.Row - 1
    
    .Range(.Cells(2, 8), .Cells(RowMax, 8)).FormulaR1C1 = _
    "=VLOOKUP(RC[-7],[SourceBook.xls]SourceSheet!C1:C6,6,FALSE)"
    
End With

[SpellBound]

Klen,

many thanks for your help. It almost does exaclty what I want. How do I tell it to put the results in a specific column on my target sheet? I have messed around with the numbers but none seem to work. Also, what is this part of the code doing:

SourceSheet!C1:C6,

Should I change it to C2:C so that it looks up all the values in that column, or am i missing the point - sorry new to this.

[SpellBound]

Hi Klen,

just to let you know this is now resolved. I changed the following values:

.Range(.Cells(2, 5), .Cells(RowMax, 5)).FormulaR1C1 = _
"=VLOOKUP(RC[-4],[CheckingMatrix.xls]MatrixResults!C1:C6,5,FALSE)"

I am still none the wiser as to what C1:C6, does, but it isn't affecting the outputs so I guess I can leave it.

Many thanks for your help

[klen_]

value at cell F2 =VLOOKUP(C2;[SourceBook.xls]SourceSheet!$C:$F;4;FALSE)
code in VBA     .Range(.Cells(2, 6), .Cells(RowMax, 6)).FormulaR1C1 = _
        "=VLOOKUP(RC[-3],[SourceBook.xls]SourceSheet!C3:C6,4,FALSE)"

'value at cell G2 =VLOOKUP(C2;[SourceBook.xls]SourceSheet!$C:$F;4;FALSE)
'code in VBA     .Range(.Cells(2, 7), .Cells(RowMax, 7)).FormulaR1C1 = _
'        "=VLOOKUP(RC[-4],[SourceBook.xls]SourceSheet!C3:C6,4,FALSE)"

You might find the following link of interest. Try these: [url="http://msdn.microsoft.com/en-us/library/dd797422.aspx"[/url]

The column C it´s C3
The column F it´s C6
[SourceBook.xls]SourceSheet!C3:C6,4