(Solved) – VBA Code to Update data from one workbook to another


I have 2 Excel Files


**Grp**   **Current Status**   **Prior Status**      **Any Changes**
  grp1      Implementation       Approved               Change 
  grp2      Approved             Approved               No Change 
  grp3      Doc Recd             Doc Recd               No Change
  grp4      Implementation       Approved               Change


 **Grp**    **Current Status**        **Col02**     
  grp1           Approved               789
  grp2           Approved               123
  grp3           Doc Recd               456
  grp4           Approved               000

I am very new to VBA , I am not sure how to write a query , so approaching the community for help .

In Trackers file if Grp has different values in status and Prior Status then Any Changes will be considered as change.

If Current Status & Prior Status are same it will be considered as No Change

for below example grp1 & grp4 Current Status has changed from Prior Status

I just want code :

  1. That Checks Change in Any Changes column “If Change”
  2. Then grp1 & grp4 Current Status from Tracker file should be updated in Inventory file Current Status column based on the Group Name.

Note: Sometimes there might be extra spaces between the both file grp field.

I do this manually and update records from Tracker to Inventory everyday.

VBA Code should activate respective files and columns and change values.

Code i tried didn’t work attaching below

Sub Lookup()

Dim rngeĀ asĀ Range
Dim cl As Range

Workbooks("Trackers 040620 PM.xlsx").Activate
lastrow = Range("A" & Rows.Count).End(xlUp).Row

   For i = 1 To lastrow

     If Range("g" & i).Value = "Change" Then
        srchval = Trim(Range("d" & i).Value)
        chgval = Trim(Range("e" & i).Value)

            Workbooks("Interim Inventory Tracker - All States 040620 v1.xlsx").Activate
            Sheets("Main Data input").Activate

             get_row_number = Workbooks("Interim Inventory Tracker - All States 040620 v1.xlsx"). _
                              Sheets("Main Data input").Range("D:D").Find( _
              What:=srchval, _
              LookIn:=xlValues, _
              LookAt:=xlPart, _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlNext, _
              MatchCase:=True _

        If get_row_number = "" Then
            'do nothing
            Workbooks("Interim Inventory Tracker - All States 040620 v1.xlsx").Activate
            Sheets("Main Data input").Range("H" & get_row_number).Value = chgval
            chgval = ""
        End If
        Workbooks("Trackers 040620 PM.xlsx").Activate
     End If
    Next i
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *