Formula or vba code to copy entire column based on text found in entire cell range

[Rahulkr]

Dear All masters, I am having some data like below:- Is there any formula or Vba code to fetch or copy entire column based on text found. Many many thanks in advance.

Column	A	B	C	D	E	F	G
Row 1	Days	D1	D2	D3	D4	D5	D6
Row 2	type	PH1	PH2	PH3	PH4	PH5	PH6
Row 3					jk
Row 4					lm
Row 5
Row 6					no
Row 7
Row 8					08:30 Ande
Row 9
Row 10					789pq
Row 11					89yy

From this data I need to copy only that entire column in which the word "ANDE" is found and paste the entire row as below in another sheet:-

It it fixed that the word which I have to search will be unique, but it is not fixed that the word which I will search will be in same column, every time it will be changing the position from cell to cell and column to column.
But the given range of data will be fixed.

Many many thanks in advance. if any one can help on this.

 

[Rahulkr]

You haven't answered the question I am trying to ask you.
Tell me the EXACT steps you are doing when this error occurs.
In order for me to recreate it, I want to do the exact same thing you are doing that causes this error to happen.

For example, I am looking for you to tell me something like:
"I am typing Ande in cell B4".
- or -
"I am copying and pasting cell C3 to C4".

no no Joe, I am not copy and pasting, I am simply typing Ande or just typing "08:30 Ande" that's all, I have tried in both way by typing without time and with time

 

[Rahulkr]

Are you sure it's a "Subscript out of range" error? I don't get that using Joe's code.
However you may get a different error depending on which version of Excel you are using.
Can you please update your account details to show your actual version of Xl rather than all the options.

Thank you Fluff for your response, please find my below version

 

[Rahulkr]

Let me check and create another new file from beginning.

 

[Rahulkr]

Dear friends, it is not working, please find the below images for the reference

 

[Rahulkr]

Below is the image of code

 

[Joe4]

Looks like you are using an older version of Excel.
See if this works better for you:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myText As String
    Dim c As Long

'   Enter value to look for
    'myText = InputBox("What string would you like to find?")
    myText = "Ande"
    
'   Locate value on sheet 1
    On Error GoTo err_chk
    c = Cells.Find(What:=myText, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    On Error GoTo 0
        
'   Copy and paste to sheet 2
    Columns(c).Copy Sheets("Sheet2").Range("A1")

    Exit Sub
    
err_chk:
'   error handling if cannot find value
    If Err.Number = 91 Then
        MsgBox "Cannot find " & myText & " on Sheet1", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
        
End Sub

 

[Rahulkr]

Looks like you are using an older version of Excel.
See if this works better for you:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myText As String
    Dim c As Long

'   Enter value to look for
    'myText = InputBox("What string would you like to find?")
    myText = "Ande"
   
'   Locate value on sheet 1
    On Error GoTo err_chk
    c = Cells.Find(What:=myText, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    On Error GoTo 0
       
'   Copy and paste to sheet 2
    Columns(c).Copy Sheets("Sheet2").Range("A1")

    Exit Sub
   
err_chk:
'   error handling if cannot find value
    If Err.Number = 91 Then
        MsgBox "Cannot find " & myText & " on Sheet1", vbOKOnly, "ERROR!"
    Else
        MsgBox Err.Number & ": " & Err.Description
    End If
       
End Sub

Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow I am so much happy

 

[Rahulkr]

Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow Just wow I am so much happy

Joe, similarly how can I do for Max and Mark in sheet 3 and 4?

 

[Fluff]

Thank you Fluff for your response, please find my below version

You are using 2010, so could you please change your account details to show just 2010 as your version. It helps us to help you.

 

[Rahulkr]

You are using 2010, so could you please change your account details to show just 2010 as your version. It helps us to help you.

Thanks Fluff, but as of now I don't have any newer version available. I told my team to provide me the newer version and now for me it is like a challenge to get it done in this version only. So, please please please please please please help me out.