Thread: Searching all worksheets and copying rows onto designated worksheet

Hi,

I have an excel sheet with 20 plus sheets filled by people from different parts of the world. Users enter their respective names and countries on 19 out of the 20 sheets.
The last sheet will contain all records from users of the same country.

I'm trying to write a macro which will compare cell "C3" of the each of the first 19 sheets ("C3" is a validation list of all countries), with cell "D6" of the last sheet.

If C3 = D6, then all non empty cells between starting from B9:M9 will be copied from sheets (1-19) to sheet 20. The macro will look for the next empty row on sheet 20 and paste matching records to avoid overwriting previous matches.

The problem is that the macro is only copying from one sheet, and keeps on looping... Please help!
Thanks in advance!

Please see code below:

Sub Country()
Dim j As Long, k As Long, r As Range
j = Worksheets.Count

For k = 1 To j
        With Worksheets(k)
            If Worksheets(k).Range("C3").Value = Sheets("Country").Range("D6").Value Then
                Worksheets(k).Select
                Range("B9:M9").Select
                Range(Selection, Selection.End(xlDown)).Select
                Selection.Copy
                Sheets("Country").Select
                Range("B5000").End(xlUp).Offset(1, 0).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Else
                MsgBox "No feedback from from " & Sheets("Country").Range("D6").Value
            End If
        End With
Next k
End Sub