CODEWITHSUNDEEP
vbaexcel
Wowdude
Wowdude

Reputation: 129

VBA to find and update row based on value

I have a master workbook and multiple children workbooks, each in fixed locations, which hold records of work as individual rows. Rows are copied from the master workbook to the children workbooks based on which of the workbooks are selected.

However, i'm stuck for the VBA coding (in macro form) whereby from within each of the children workbooks, they can update the master. I need it to find and update the work row in the master based on the unique ID number which is assigned to each piece of work and appears in the same column (column D) in both the children and master workbooks.

Any help or ideas would be much appreciated.

Thanks in Advance

Apologies. Please see below data from my sample child workbook (sorry I couldn't format it properly) and below that, the current VBA code I have to copy back to the master workbook:

Data:

Complaint Type  Raised by   Status      ID
Billing         Percy       Completed   101
Billing         Percy       Completed   102
Metering        John        Pending     103
Reads           John        Pending     104
Reads           Jack        Pending     105
Billing         Julie       Untouched   106
Service         Jack        Completed   107
Metering        Julie       Untouched   108
Service         Percy       Pending     109
Payment         Pete        Pending     110

VBA Code:

Private Sub CommandButton21_Click()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

 Dim SourceRange As Range, DestRange As Range

 Set SourceRange = Sheets("Sheet1").Range("A2:D2") 'data source
 wb = ActiveWorkbook.Name

 Workbooks.Open "C:\Users\user\Desktop\Test.xlsm" 'path to Master
 Windows(wb).Activate 'Activate Child Workbook
 SourceRange.Cut 'define the range to copy 'Cut data from child workbook

 Windows("Test.xlsm").Activate 'Activate Master
 Sheets("Completed").Select 'Activate Sheet
 Workbooks("Test.xlsm").Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1).Paste 'Paste in Master

 Application.CutCopyMode = False 'Clear Clipboard

 End Sub

Upvotes: 1

Views: 15542

Answers (1)

Calum
Calum

Reputation: 143

The below code can go in each of your children workbooks, I wasn't sure where the ID appeared in the master workbook so I just assumed column D the same as the children, the below is untested and based on if column D in the child matches column D in the master it will update column A, B and C. At the moment it only does it for 2000 rows, change if applicable. :)

Dim fpath As String
Dim owb As Workbook
Dim Master As Worksheet 
Dim Slave As Worksheet 'the following declares both master and slave as worksheets

fpath = "location of master workbook" 


Set owb = Application.Workbooks.Open(fpath) 'opens the file path

Set Master = ThisWorkbook.Worksheets("name of sheet in child workbook") 'declares this workbook and sheet as "master"
Set Slave = owb.Worksheets("name of sheet in master you are pasting to") 'declares the workbook and sheet you're copying to as "slave"


For j = 1 To 2000 '(the master sheet) 'goes through each row from 1 to 2000

For i = 1 To 2000 '(the slave sheet) 'again does the same and the slave sheet
    If Trim(Master.Cells(j, 4).Value2) = vbNullString Then Exit For 'if the ID is blank it will exit and move on to the next row
    If Master.Cells(j, 4).Value = Slave.Cells(i, 4).Value Then 'the 4 represents column D, if cell in column D matches the cell in column D in the masterwork book then it will..
            Slave.Cells(i, 1).Value = Master.Cells(j, 1).Value 'cell in column A child workbook equals cell in column A in master workbook
            Slave.Cells(i, 2).Value = Master.Cells(j, 2).Value
            Slave.Cells(i, 3).Value = Master.Cells(j, 3).Value 'same for B and C


    End If
    Next


Next


MsgBox ("Data Transfer Successful")

With owb
.Save
.Close
End With

Upvotes: 1

Related Questions