Separate Rows Based on Content & Place in Different Worksheets

[Small Paul]

Hi

I have a worksheet [report] which has defining content in column A, e.g.

A1 - 5
A2 - 5
A3 - 7
A4 - 8
A5 - 8 etc

I need to have a new worksheet opened for each (5,7,8) and the content placed in row 10 of the tab.

Please, can anyone help?

Regards
Small Paul.

 

[Fluff]

A few questions
Does your sheet have a header row? if so is it in row1?
What is the last used column in the sheet?
When you say for content to be in row 10, do you mean starting in row 10 & going down?

 

[Small Paul]

Hi Fluff
Taking your questions in order:
1) yes, the initial downloaded sheet has a header, so data starts in Row 2
2) the last column is H
3) the tabs I need opened will have a number of header rows pertaining to the client(s), transaction in question, date etc. I am not concerned about this as I think I can sort this.
Cheers
Small Paul.

 

[Fluff]

How about

Sub CopyFilter()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("A2", Ws.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            Ws.Range("A1:H1").autofilter 1, Cl.Value
            Ws.autofilter.Range.Offset(1).Copy Sheets(Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next Cl
   End With
   Ws.AutoFilterMode = False
End Sub

If your col A data is numbers then this will need a few tweaks

 

[Small Paul]

Column A example is: [TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 256"]BBVA 70-70 Low Hurdle Autocall July 2018 EUR (XS1838127345)
What occurs is:
The first batch of matching items remain on original worksheet.[/TD]
[/TR]
</tbody>[/TABLE]
The remainder are hidden

 

[Fluff]

Did you get any errors?

 

[Small Paul]

Run-time error '9':
Subscript out of range

 

[Fluff]

On which line of code?

 

[Small Paul]

  Ws.AutoFilter.Range.Offset(1).Copy Sheets(Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[Fluff]

Is this all in one cell

BBVA 70-70 Low Hurdle Autocall July 2018 EUR (XS1838127345)