Updating Cells after comparing two worksheets

[Monomeeth]

Hello

I have created a macro which automatically compares the data in two
Worksheets, Worksheet A and Worksheet B. It does this by using the first
column as a reference (this column contains a unique identifier). The macro
identifies: (1) new data by copying it into a worksheet named "Additions",
(2) ignored data by copying it into a worksheet named "Ignored", and (3)
changed data by copying it into a worksheet named "Changed".

I now want to create a second macro which will automatically update
Worksheet B by: (1) adding the data in the "Additions" worksheet, and (2)
modifying the cells in only one column for all the rows listed in the
"Changed" worksheet.

DESCRIPTION
Worksheet B contains 20 columns, whileas the "Changed" worksheet only
contains 10 columns. These 10 columns have the same headings as the first 10
columns in Worksheet B. The 10th column (i.e. column "J") is headed "End
Date" and this is the only difference between the rows in Worksheet B and the
"Changed" worksheet. In other words, the records listed in the "Changed"
worksheet are only listed because their "End Date" has changed in comparison
to Worksheet B.

PROCESS
I now want a macro to automatically update the "End Date" of all these
records within Worksheet B without changing the contents of columns 1 to 9 or
the contents of columns 11-20. So the Macro needs to identify only those rows
in Worksheet B sharing the same unique identifier as those listed in the
“Changed†worksheet, and update the "End Date" field in these rows with the
relevant dates found in the corresponding rows within the "Changed" worksheet.

The macro also needs to add to the bottom of Worksheet B the all rows
contained in the “Additions†worksheet.

I hope this all makes sense.

Your help would be greatly appreciated.

 

[carlo]

maybe these two subs help you.

Sub ChangeRecords()

Dim WS_S As Worksheet
Dim WS_T As Worksheet
Dim TColumn As Integer
Dim UColumn As Integer
Dim Rng As Range

Set WS_S = Worksheets("Sheet2")
Set WS_T = Worksheets("Sheet1")
TColumn = 10
UColumn = 1

For i = 2 To WS_S.Cells(65536, UColumn).End(xlUp).Row

Set Rng = WS_T.Columns(1).Find(What:=WS_S.Cells(i, UColumn), _
After:=WS_T.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

Rng.Offset(0, TColumn - 1).Value = WS_S.Cells(i, TColumn)

Next i

End Sub

Sub AddRecords()

Dim WS_S As Worksheet
Dim WS_T As Worksheet

Set WS_S = Worksheets("Sheet3")
Set WS_T = Worksheets("Sheet1")

WS_S.Range("A2:J" & WS_S.Cells(65536, 1).End(xlUp).Row).Copy _
WS_T.Range("A" & WS_T.Cells(65536, 1).End(xlUp).Row + 1)

End Sub

Change the ranges and stuff according to your Project.

Hope there are no wordwraps.

Cheers
Carlo

 

[Lazzzx]

Hi

Made these two routines that shold do your job. I asume that there are no
empty rows in your data. Furthermore there should be no column headers in
"Changes" and "additions". In that case it will not work with the
..currentRegions.

regards
Laxxxz


Sub addAdditions()
Dim sourceRange As Range
Dim targetRange As Range

Set sourceRange = Sheets("Additions").Cells(1, 1).CurrentRegion
Set targetRange = Sheets("Worksheet B").Cells(1, 1).End(xlDown).Offset(1,
0).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)

For x = 1 To sourceRange.Cells.Count
targetRange(x) = sourceRange(x)
Next

End Sub

Sub updateChanges()
Dim rangeB As Range
Dim rangeChanges As Range

'setting rangeB to the first column on sheet "Worksheet B"
With Sheets("Worksheet B").Cells(1, 1).CurrentRegion
Set rangeB = .Resize(.Rows.Count, 1)
End With

'setting rangeB to the first column on sheet "Changes"
With Sheets("Changes").Cells(1, 1).CurrentRegion
Set rangeChanges = .Resize(.Rows.Count, 1)
End With

For Each cll1 In rangeChanges
For Each cll2 In rangeB
If cll1 = cll2 Then cll2.Offset(0, 9).Value = cll1.Offset(0,
9).Value

Next
Next


End Sub

 

[broro183]

Hi,

I haven't looked in depth at Lazz's suggested code or the Op's request and I
agree with Lazz's assumption that there should be no empty rows within the
data. However, I disagree with coding on the assumption that entire sheets
have no Headers because sheets set up this way can be easily questioned
regarding their data validity (& the OP's description does state that one of
the sheets has the same headers) .

Lazz,
The below code should overcome the limitations of ".currentregion",
provided, (dare I say it, another assumption is met ;-) ) , that the header
is completely contained in a single row at the top of the current region.
This can be adapted by changing the sheet name for the other sheet.

With Sheets("Cost Sheet").Cells(1, 1).CurrentRegion
Set SourceRange = .Offset(1, 0).Resize(.Rows.Count - 1)
End With


hth
Rob

 

[Monomeeth]

Hi Everyone

I was able to use your suggested coding to get the whole thing working
beautifully. I'm thanking all of you because I ended up having to use bits
and pieces of your various samples to get the macro to where it is now, plus
some of the coding in other macros.

Thank you again,

Joe.