PDA

View Full Version : How to extract specified data to the other worksheet?



zetus
01-30-2017, 09:13 PM
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
01-31-2017, 02:43 AM
autofilter?

https://support.office.com/en-us/article/Quick-start-Filter-data-by-using-an-AutoFilter-08647e19-11d1-42f6-b376-27b932e186e0

vcoolio
01-31-2017, 02:47 AM
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.