VBA: Search in column for specific text and copy to another tab

[TaskMaster]

Hi all and thank you in advance for looking at my query. I have tried to make a start with this but getting nowhere.

I have a tab called data which I currently have to manually format copy and paste to another tab. In column where the first 4 characters in a cell is = to "FB09" I want to copy the last 8 characters data into another tab called summary starting in cell A23. Could anyone give me some suggestions.

 

[rlv01]

And if cell A23 already has data in it?

 

[TaskMaster]

Hi thank you for your question. A23 would be empty at the start of the procedure and would like it to sequentially fill each row below for any further data items.

 

[rlv01]

One way.

Sub FindAndCopy()
    Dim WS As Worksheet
    Dim rng As Range
    Dim S As String, SearchStr As String
    Dim LastRow As Long

    Set WS = Worksheets("data")
    SearchStr = "FB09"

    With WS.Range("A2", WS.Range("A" & WS.Rows.Count).End(xlUp))
        Set rng = .Find(what:=SearchStr, after:=.Cells(.Cells.Count), lookat:=xlPart, MatchCase:=True, searchdirection:=xlNext)
    End With

    If Not rng Is Nothing Then
        If InStr(rng.Value, SearchStr) = 1 Then
            S = Right(rng.Value, 8)

            Set WS = Worksheets("summary")
            If WS.Range("A23").Value = "" Then
                WS.Range("A23").Value = S
            Else
                With WS
                    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                    .Range("A" & LastRow + 1).Value = S
                End With
            End If
        End If
    Else
        MsgBox "'" & SearchStr & "' not found", vbExclamation
    End If
End Sub

 

[TaskMaster]

One way.

Sub FindAndCopy()
    Dim WS As Worksheet
    Dim rng As Range
    Dim S As String, SearchStr As String
    Dim LastRow As Long

    Set WS = Worksheets("data")
    SearchStr = "FB09"

    With WS.Range("A2", WS.Range("A" & WS.Rows.Count).End(xlUp))
        Set rng = .Find(what:=SearchStr, after:=.Cells(.Cells.Count), lookat:=xlPart, MatchCase:=True, searchdirection:=xlNext)
    End With

    If Not rng Is Nothing Then
        If InStr(rng.Value, SearchStr) = 1 Then
            S = Right(rng.Value, 8)

            Set WS = Worksheets("summary")
            If WS.Range("A23").Value = "" Then
                WS.Range("A23").Value = S
            Else
                With WS
                    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                    .Range("A" & LastRow + 1).Value = S
                End With
            End If
        End If
    Else
        MsgBox "'" & SearchStr & "' not found", vbExclamation
    End If
End Sub

Unfortunately I get the error 'FB09' not found. If I do the find manually it is found. I recorded the following and this worked too. Do you have any ideas?

    Columns("S:S").Find(What:="FB09", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate

 

[rlv01]

Please post your column S data in a form that can be copied so I can test my code against it. If the data contains anything sensitive, then replace it with dummy data.

I recommend using xL2BB.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[TaskMaster]

TEST.xlsm
	S	T
1	Narrative	Customer reference
2	FB0947704537522	FB0947704537522
3	FB0960939455541	FB0960939455541
4	FB0956903531552	FB0956903531552
5	FB0983143801318	FB0983143801318
6	FB0967970038505	FB0967970038505
7	FB0917724215771	FB0917724215771
8	FB0952434554610	FB0952434554610
9	FB0989962911620	FB0989962911620
10	FB0931218720619	FB0931218720619
11	FB0980596412871	FB0980596412871
12	FB0918470040691	FB0918470040691
13	FB0910948105013	FB0910948105013
14	FB0985948066972	FB0985948066972
15	FB0979225549536	FB0979225549536
16	FB0973563849218	FB0973563849218
17	FB0925868354143	FB0925868354143
18	ST1118627945595	ST1118627945595
19	ST1126231450826	ST1126231450826
20	ST1187294739536	ST1187294739536
21	ST1112982251172	ST1112982251172
22	ST1151393551196	ST1151393551196
23	CV2484567100327	CV2484567100327
24	CV2487325486059	CV2487325486059
Data

 

[Alex Blakenburg]

Does rlv01's code work if you change the find range to be S instead of A ?

    With WS.Range("S2", WS.Range("S" & WS.Rows.Count).End(xlUp))

 

[TaskMaster]

Does rlv01's code work if you change the find range to be S instead of A ?

    With WS.Range("S2", WS.Range("S" & WS.Rows.Count).End(xlUp))

Hi yes!

This has worked for the first instance. But doesn't pick up any subsequent instances. Is there a way to add this in?

Many thanks

 

[rlv01]

Is it just column S that you want to search for FB09 in?