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 Submessage edited by Usman.mirza.pk
A little mistake is there in the title, a word is "Find" not "Fint"
Any one who can help me?
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.
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.
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 )
Ad Choices