VBA comparing sheets and cells

[maurice broring]

Hi All,

Can someone please help me out on the following.

two sheet are identical. (same amount of colums with same headers)
sheet 1 has statusses of orderid's (orderid's are in column A, rest of
data in column B:AE)

I run a macro that collects data from other sheets and external files
The Data is placed in an empty "statusses" sheet (sheet 2), starting
with orderid's in column A, rest in B:AE

Now I need a code that checks in column A sheet 2, starting at row 2
and searches in sheet 1 the whole column A for a identical orderid.
If found, then it copys the entire row from sheet 2 to sheet 1
(overwrites row in sheet 1 the orderid (which is the same) with all
data in de cells behind B:AE)
When ready or if not found the same orderid it checks the next row
(column A) in sheet 2.

Thank you in advance for helping me out.

Regards,
Maurice

 

[Vacuum Sealed]

Hi Maurice

This is not the most prettiest of codes, but it works for what you want.
Someone else may have a cleaner code to use.

Sub Compare()

Dim sSht As Worksheet, tSht As Worksheet
Dim i As Integer, j As Integer

For j = 2 To 100 'Resize to suit
Set tSht = Sheets("Sheet1")

For i = 2 To 100 'Resize to suit
Set sSht = Sheets("Sheet2")

If sSht.Range("A" & i) = tSht.Range("A" & j) Then
sSht.Range("A" & i).Resize(i, 30).Copy 'This copies A to AE
tSht.Activate
tSht.Range("A" & j).Select
ActiveSheet.Paste
End If
Next i
Range("A1").Select

Next j

sSht.Activate
Application.CutCopyMode = False
Range("A1").Select

End Sub


I tried to shorten the Copy/Paste to this:

sSht.Range("A" & i).Resize(i, 30).Copy (tSht.Range("A" & j))

But it did not like it and halted on it so I stepped through it
individually as above.

HTH
Mick.