Separate Rows Based on Content & Place in Different Worksheets

Small Paul

Board Regular
Joined
Jun 28, 2018
Messages
118
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Upvote 0
How about
Code:
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
 
Upvote 0
Column A example is:
BBVA 70-70 Low Hurdle Autocall July 2018 EUR (XS1838127345)
What occurs is:
The first batch of matching items remain on original worksheet.

<tbody>
</tbody>
The remainder are hidden
 
Upvote 0
Code:
  Ws.AutoFilter.Range.Offset(1).Copy Sheets(Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
 
Upvote 0
Is this all in one cell
Code:
BBVA 70-70 Low Hurdle Autocall July 2018 EUR (XS1838127345)
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top