Hi Oldbrewer,
I am new to vbscripting/macros. I have tried a code (below) mentioned in one of the posts which is similar to my request. But, I am ending up with a data adding in a single row in master sheet instead of adding all the newly added data in source workbook to master workbook. Please let me know if I can do something in the below code so that if there is a change in source sheet, the same has to be updated in master sheet.
Sub Test()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim Rng As Range
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim Cell As Range
Dim Target As Range
Dim r As Long
Set wbSource = Workbooks("Source.xls")
Set wsSource = wbSource.Worksheets("Sheet1")
With wsSource
Set Rng = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
End With
Set wbMaster = Workbooks("Master.xls")
Set wsMaster = wbMaster.Worksheets("Sheet1")
With wsMaster
For Each Cell In Rng
With .Columns("B")
Set Target = Nothing
Set Target = .Find(What:=Cell.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With
If Not Target Is Nothing Then
r = Target.Row
Else
r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End If
.Range("A" & r).Value = Cell.EntireRow.Range("C" & 1).Value
.Range("B" & r).Value = Cell.EntireRow.Range("D" & 1).Value
.Range("C" & r).Value = Cell.EntireRow.Range("I" & 1).Value
.Range("D" & r).Value = Cell.EntireRow.Range("J" & 1).Value
.Range("E" & r).Value = Cell.EntireRow.Range("M" & 1).Value
.Range("F" & r).Value = Cell.EntireRow.Range("N" & 1).Value
.Range("I" & r).Value = Cell.EntireRow.Range("P" & 1).Value
.Range("J" & r).Value = Cell.EntireRow.Range("Q" & 1).Value
Next Cell
For r = .Range("B" & .Rows.Count).End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(Rng, .Range("b" & r).Value) = 0 Then
.Rows(r).Delete
End If
Next r
End With
End Sub
Thank you!
