• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy the excel sheet output to another sheet using macros

aravind1435

New Member
Hi all

I am new macros and when i am searching some thing regarding it i came across your page which was so helpful,

I need some help from you, i got macros file from this page,regarding the searching the value in the in the entire excel sheet, while going to that macros sheet i got 2 ideas the value which you got as output ,

Idea 1:-

Using that can we fetch the entire value of that row and copy in the another sheet one after the other.

Idea 2:-

In the search option if i had provided the input example like abc,xyz,123 can it show the proper output as present, if it can then i want to apply Idea 1 also here

Note:- In my raw data might be the value will be separated by "," ":" ";" any special characters, it should satisfy it and provide me the output.

If we can do, can you please advice me how to do it, so that it will helpful for me. Providing the file which you had done in past for your reference.

Hope my mail is clear to you. Awaiting your response

thanks in advance.

Regards
Aravind
 

Attachments

  • find-and-extract-all.xlsm
    108.6 KB · Views: 11
Hi !​
  1. It could be so easy with an attachment with the source data worksheet and a second worksheet filled with a sample
    far better than an empty hidden worksheet !

  2. Easy just using the Split VBA text function combined with a For Each loop in order to treat each one.
    So with an attachment with both worksheets …
 
Hi Ninja

As I mentioned I am not sure how to use those function, request to update the code for the macros file provided so that I will see it and understand it

Thanks in advance

Regards
Aravind
 
As I wrote the code will depend on the second worksheet layout, the reason why I requested a new attachment with both worksheets filled …​
Or without any attachment the « how to use those function » is explained in the VBA inner help with its example like in forum threads.​
A smart way better than « searching the value in the in the entire excel sheet » is to use an easy Excel basics feature like a filter​
or best here to copy to another worksheet is an advanced filter which requests an unique codeline (see the AdvancedFilter VBA help)​
but searching on a column : to search a country it's not clever to search in all columns as only the Location column is enough !​
Sample in this thread according to « I will see it and understand it » …​
 
HI Ninja, i am total got confuse with the statement which you had provided above, can you please explain me in lamin language
 
Hi Marc

thanks for the update

By doing some R&D , i received some extent of my request, but got trucked in some more level.

1.While copying from one sheet header or missing , please guide me
2,Intial request was if i search any string it should filter it in the entire sheet and copy and paste in another sheet, but in my case, it getting copied but in the same time if the input string is part of 2 column then it was created two rows instead of one(refer the attachment) example search for "aravind".
3.Can i provide multiple input through input box by comma separated, if yes then the output should be filter by the same

example (input 1 have 5 records matching and input 2 had 10 records matching)then it should provide output in the second sheet as 1 row as 1st input and related output and after that row 2nd input and corresponding filtered data

can you please advice how it possible.
 

Attachments

  • Main file.xlsm
    144.9 KB · Views: 6
1.While copying from one sheet header or missing , please guide me
If using an advanced filter, that requires the destination worksheet already has some headers,​
whatever the order but with exactly the same spelling …​
2, […] example search for "aravind".
As 'aravind' no matches within the data it's difficult to see the light …​
3.Can i provide multiple input through input box by comma separated
Yes if the code splits the input in order to use each value within a search loop …​
But the easy and smart way (by column obviously to avoid duplicates rows in the result) is to not use an input box​
but a specific range area like in my sample workbook of the post #4 link then as I yet wrote using​
an advanced filter requests only a single codeline as you must yet see in my sample workbook.​
 
If using an advanced filter, that requires the destination worksheet already has some headers,​
whatever the order but with exactly the same spelling …​
thanks for the feedback but the advanced filter which you posted in the previous post is need to changes in excel setting everytime if there was any new column if i am not wrong, in that case it will not easy for me , mine should be fetech from the macros itself​

As 'aravind' no matches within the data it's difficult to see the light …​
sheet which i had provided in my last post consists of aravind in the row of sheet , please click on find and extract and serach for aravind and go to sheet to copysheet where you can find 2 rows for aravind , where the raw data consits of one row for aravind​

Yes if the code splits the input in order to use each value within a search loop …​
But the easy and smart way (by column obviously to avoid duplicates rows in the result) is to not use an input box​
but a specific range area like in my sample workbook of the post #4 link then as I yet wrote using​
an advanced filter requests only a single codeline as you must yet see in my sample workbook.​
thanks for the post 4, in that you had given multiple drop down , my requirement was only one text box separted by Cooma​
Hope it was clear.​
 
hi guyz

please help on this

Code:
Sub findAll()
    Dim findWhat As String, address As String, title As String, splittext() As String, test As String
    Dim fsr As Range, rs As Range, fCount As Long
   
    findWhat = InputBox("Enter what you want to find?", "Find what...")
    If Len(findWhat) > 0 Then
        clearFinds
        splittext = Split(findWhat, ",")
        'first splited text
        If Len(splittext(0)) > 0 Then
        Set frs = Range("b4").CurrentRegion
        Set rs = frs.Find(What:=findWhat)
        If Not rs Is Nothing Then
        address = rs.address
        Do
        fCount = fCount + 1
        Sheets("Original").Rows(rs.Row).Copy Sheets("CopySheet").Rows(fCount)
        Set rs = frs.FindNext(rs)
        Loop While Not rs Is Nothing And rs.address <> address
         End If
         MsgBox "Search Results Copied..."
        End If
    End If
End Sub
Sub clearFinds()
Sheets("CopySheet").Cells.ClearContents
End Sub

the above is helping me to retrive only even i splitby comma, not sure how send the second input
 
Last edited by a moderator:
some how for the above code i had found the answer by studying in youtube,

another challenge is stopping me, can any one help me out

chanllange:- if a row has duplicate value , i need to copy one time that row to another sheet how can i do that
 
<code>
Code:
Sub findAll()
    Dim findWhat As String, address As String, title As String, splittext() As String, inputvalue As String
    Dim fsr As Range, rs As Range, fCount As Long, counter As Integer, inputdata As Range, flash As String
   
   
    findWhat = InputBox("Enter what you want to find?", "Find what...")
   
    If Len(findWhat) > 0 Then
        clearFinds
        splittext = Split(findWhat, ",")
        For counter = LBound(splittext) To UBound(splittext)
        inputvalue = splittext(counter)
        Set frs = Range("b4").CurrentRegion
        Set rs = frs.Find(What:=inputvalue)
        If Not rs Is Nothing Then
        address = rs.address
        Do
        fCount = fCount + 1
        Sheets("Original").Rows(rs.Row).Copy Sheets("CopySheet").Rows(fCount)
        Set rs = frs.FindNext(rs)
        Loop While Not rs Is Nothing And rs.address <> address
        End If
' need to understand why cell value doesnot highlight
        Set inputdata = ThisWorkbook.Worksheets("CopySheet").UsedRange
        For Each Cell In inputdata.Cells
        If Cell.Text = inputvalue Then
        Cell.Interior.Color = 44
        End If
        Next
        Next counter
        MsgBox "Search Results Copied..."
     End If
   
End Sub
Sub clearFinds()
Sheets("CopySheet").Cells.ClearContents
End Sub
</code>
 
Last edited by a moderator:
Hi All

Finally, I reached the last stage of my request, only pending was whenever I give the input data, that data should save in another sheet(Summary sheet as provided in attachment)
 

Attachments

  • Search_Main.xlsm
    488 KB · Views: 7
Back
Top