VBA for updating a from another workbook.

[Josephkymer]

Hi

I am very new to vba and I am looking for the following.

We have 2 workbooks, Master.xls and Feed.xls. Both have an unique identifiers in columnA.
I am looking for a vba code to match the unique identifiers in columnA and update the data in columns L,O and S. Master.xls has more rows than Feed.xls. So, Only the rows which match should be updated and only in the above mentioned columns.

I tried everywhere.
Please help me

 

[Fluff]

A few questions
1) Do you want to copy cols L,O & S from feed to master, or the otherway round?
2) What are the sheet names to look at in each file?
3) Will both workbooks be open, or do you need the macro to open one of them?

 

[Josephkymer]

1. Master should be updated from the Feed. Not the entire columns but only the matching cells.
2. Both are on Sheet1.
3. Master is opened. Feed should be opened by vba. And closed after updating.
4. I do not know the path for Feed, but if you can construct the path as from desktop, I can change it.

Thank you

 

[Fluff]

Ok, how about

Sub UpdateData()

   Dim Fwb As Workbook
   Dim Mws As Worksheet
   Dim Fws As Worksheet
   Dim Pth As String
   Dim Cl As Range

Application.ScreenUpdating = False
   Pth = Environ("userprofile")
   Set Mws = Workbooks("Master.xls").Sheets("Sheet1")
   Set Fwb = Workbooks.Open(Pth & "\desktop\Feed.xls")
   Set Fws = Fwb.Sheets("Sheet1")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Fws.Range("A2", Fws.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 11).Value & "|" & Cl.Offset(, 14).Value & "|" & Cl.Offset(, 18).Value
      Next Cl
      For Each Cl In Mws.Range("A2", Mws.Range("A" & Mws.Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then
            Cl.Offset(, 11).Value = Split(.Item(Cl.Value), "|")(0)
            Cl.Offset(, 14).Value = Split(.Item(Cl.Value), "|")(1)
            Cl.Offset(, 18).Value = Split(.Item(Cl.Value), "|")(2)
         End If
      Next Cl
   End With
   Fwb.Close False
End Sub

 

[Josephkymer]

Thank You. I will test it today.
Thanks again