VBA

Hello Sanjhali,

I have some suggested solution code for you. Please tell us if this doesn't work for you. Remember to adjust the references as needed.

1.

Sub Class_Type()

Sheets("Info").Columns("A:D").AutoFilter Field:=3, Criteria1:="C"
Sheets("Info").Range("a1").SpecialCells(xlCellTypeVisible).Cells.
Copy
Sheets("Class C").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Info").Range("A1").AutoFilter

End Sub

*This code uses a filter instead to find the Cs in the “Info” worksheet, then dynamically selects, copies and pastes the results into the “Class C” worksheet.

2.

Sub Country_Lookup()

Dim myRange As Range

Set myRange = Sheets("Master Country").Range("A1").CurrentRegion

Sheets("Info").Activate

Range("A2").Select

Do Until ActiveCell = ""

If ActiveCell.Offset(0, 3) <> WorksheetFunction.VLookup(ActiveCell, myRange, 4, 0) Then
ActiveCell.Offset(0, 3).Interior.Color = vbYellow
ActiveCell.Offset(0, 3) = WorksheetFunction.VLookup(ActiveCell, myRange, 4, 0)
End If

ActiveCell.Offset(1, 0).Select

Loop

End Sub

*This code uses a Vlookup to compare the countries in the “Info” sheet to those in the “Master Country” sheet. The cells with differences are shaded in yellow and the incorrect countries are replaced by the correct countries.

I hope this helps.

Kind regards
Marius Barnard
STL