Find the text in the string and filter by word. Then copy the filtered content to a new sheet

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi Guys,
(All EXCEL.LENT ladies and gentlemen)

I have text in Column A.
Here are the rows of the table that start with the following characters:
<A1 ...
For example: <A1 S = "20" D = "5.70" Z = "15.68" Date = "2021-03-25" F = "1000615" Abj = "DE09876547" />
Then the lines follow
<B2
<C1

The task:
I need to find a table row, with the word: "<A1 ..."
I then copy (filter) all these lines and paste them into the second sheet.

The same happens when searching for other terms <B2 ..., <C1 ...
etc.

How to solve it in VBA?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
VBA Code:
Sub FilterTest()
' Filtering by
    Sheets("Source").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$A$290"), , xlNo).Name = _
        "Table4"
      
    'I need to change this row because the table will have a variable number of rows and table4 will always have a different name _
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$A$290"), , xlNo).Name = "Table4"
      
    Range("Table4[[#All],[Stĺpec1]]").Select ' Výber tabuľky
  
    ' Selection by criteria "<A1"
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:= _
        "=*<A1*", Operator:=xlAnd
    Selection.Copy
    Sheets("A1").Select
    Range("A1").Select
    ActiveSheet.Paste
  
  
    ' Selection by criteria "<B2"
    Sheets("Source").Select ' Table selection
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:= _
        "=*<B2*", Operator:=xlAnd
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("B2").Select
    ActiveSheet.Paste
  
    ' Selection by criteria "<C1"
    Sheets("Source").Select ' Table selection
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1
    ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:= _
        "=*<C1*", Operator:=xlAnd
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("C1").Select
    Range("A1").Select
    ActiveSheet.Paste

End Sub
 

Slavio

New Member
Joined
Mar 28, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I finally solved it like this, So if anyone needs the code ~> here it is and it is functional for me.

The data is not transferred to the second sheet, but to column T. Then I copy and paste it. It's not an elegant solution, but it works :unsure:

VBA Code:
Sub AddFormulaTo()
Dim cell As Range
For Each cell In Range("A1:A1000")
If cell.Value = "<A1 S=" Then
cell.Offset(0, 19).Value = "=CONCATENATE ..."
ElseIf cell.Value = "<B2" Then
cell.Offset(0, 19).Value = "=CONCATENATE ..."
ElseIf cell.Value = "<C1" Then
cell.Offset(0, 19).Value = "=CONCATENATE ..."
End If
Next cell

    Sheets("Sheet1").Select
    Columns("T:T").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,530
Messages
5,636,861
Members
416,946
Latest member
mniceguy81

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top