How to extract specified data to the other worksheet?

[zetus]

I have two worksheet: sheet1 and sheet2

sheet1:
name class country
abc 1b usa
bbc 1a china
bds 2b usa
......

sheet2:
name class country

if i have a number of data and want to select countries field 'usa' and show them in sheet2 one by one, how can i do it? thank you so much.

sheet2:
name class country
abc 1b usa
bds 2b usa

[mana]

autofilter?

https://support.office.com/en-us/art...6-27b932e186e0

[vcoolio]

Hello Zetus,

Try the following code placed in a standard module (untested):-

Sub Transfer()
Dim cSrch As String
Application.ScreenUpdating = False
cSrch = InputBox("Please enter the required country.")
If cSrch = vbNullString Then Exit Sub
With Sheet1
    .AutoFilterMode = False
    With Sheet1.Range("C1", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp))
        .AutoFilter 1, cSrch
        On Error Resume Next
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
    End With
    .AutoFilterMode = False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

When the code is run, an input box will appear asking you which country you wish to search for. When the country is entered and you click OK, or press Enter, the relevant rows of data for the required country will be transferred to Sheet2. The code is not case sensitive and can be run from any sheet. It also uses autofilter which is a better option for large data sets.

I hope that this helps.

Cheerio,
vcoolio.