I am currently using the code below to find multiple values and the copy/paste into another sheet. The purpose of this code is to retrieve a list of genes (small words) from huge data set. I tell you the description of this code:
This code is for one gene list (a list of small keywords) only, which is present in sheet3 column 1 (beginning with A2). All the data which retrieve from Huge Data set (present in sheet 1) is copy on the sheet 2. As a result new sheet is made named "sheet 2".
Now I have 6 gene lists (6 lists of small keywords), now i have to make 6 separate sheets for all these gene lists, Like I did in previous work for making only one sheet that is sheet 2. Now here, I want 6 separate sheets for each gene list. Kindly tell me the code for this problem.
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column B, copy it too the next row in Sheet2
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("B" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
message edited by Usman.mirza.pk