Click here for important information about Computing.net.

Fint multiple words and paste them in different sheets

August 16, 2013 at 17:47:53
Specs: Windows 7
Hi,
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.

Sub OrderFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'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
With Sheets(1).Columns("B")
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)
End If
Next
End With
End Sub

message edited by Usman.mirza.pk


See More: Fint multiple words and paste them in different sheets

Report •

#1
August 17, 2013 at 05:09:42
A little mistake is there in the title, a word is "Find" not "Fint"

Report •

#2
August 17, 2013 at 11:19:00
Any one who can help me?

Report •

#3
August 17, 2013 at 13:16:29
Be patient, my friend, be patient. We're all volunteers here and have lives to live.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions
See More ↓

#4
August 19, 2013 at 13:19:15
Do the 6 sheets where the data is to be pasted already exist or does the code have to create them each time?

Do the sheets have/need names?

Where are the 6 gene lists...Sheet 3 Columns A:F or someplace else?

The more detail you can provide, the more chance we can offer a working solution.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 21, 2013 at 09:15:33
1st answer: sheet can be created manually, ( like in the previous work, i made "sheet2" in which the the data pasted ) so in this current problem, i will create the 6 sheets manually.

2nd answer: No, sheets don't have/need any names

3rd: Yes, 6 gene lists are present in Sheet3 columns A:F (beginning with A2, B2, C2, D2, E2, F2 )


Report •
Start a discussion

Ask Question