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.

 

[Small Paul]

Yes it is, starting in cell A2.
This is the key variable for which a new worksheet is required when different

 

[Fluff]

Firstly I think I misunderstood. I thought that the sheets already existed
Secondly sheet names are limited to 31 characters
The macro can create the sheets using the first 30 characters if that's ok

 

[Small Paul]

More likely my bad explanation!
Not too fussed as to the naming of the worksheets. In an ideal world it would everything within the brackets.

 

[Fluff]

How about

 

[Small Paul]

Hi Fluff
Sorry, delay due to being away on work conference!
Please could you expand on on post 14
Small Paul

 

[Fluff]

It would help if I had supplied the 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, Split(Split(cl.Value, "(")(1), ")")(0)
            Sheets.Add(, Sheets(Sheets.Count)).name = .Item(cl.Value)
            Ws.Range("A1:H1").AutoFilter 1, cl.Value
            Ws.AutoFilter.Range.Offset(1).Copy Sheets(.Item(cl.Value)).Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next cl
   End With
   Ws.AutoFilterMode = False
End Sub

 

[Small Paul]

Hi Fluff
Many thanks.
The code places the data on new worksheets (and names them) which is brilliant.
How do i change it so that the data starts on row 6?
The reason is, I need to apply headers to each page:

    Columns("A:A").ColumnWidth = 74.86    Columns("B:B").ColumnWidth = 25.43
    Columns("C:C").ColumnWidth = 17.71
    Columns("D:D").ColumnWidth = 20.29
    Columns("E:E").ColumnWidth = 21.86
    Columns("F:F").ColumnWidth = 17.29
    Range("B2").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
    Selection.NumberFormat = "_-* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL] -;-* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL] -;_-* ""-""??_-;_-@_-"
    Range("B6:B500").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
    Selection.NumberFormat = "_-* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL] -;-* #,#[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=0_]#0_[/URL] -;_-* ""-""??_-;_-@_-"
    Range("F6:F500").Select
    Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
    Range("D2").Select
    Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Product Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Total Received"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Strike Date"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "Product Name"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "Nominal"
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "Price %"
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "Life Co"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "Policy / Account No."
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "Order Placed"
    subt = WorksheetFunction.Sum(Range("B6:B500"))
    Range("B2" & lastrow).Value = subt
    Rows("1:1").Select
    Selection.Font.Bold = True
    Rows("5:5").Select
    Selection.Font.Bold = True
        Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    Application.PrintCommunication = False
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 10
        End With
        End With
End Sub

This simply sets up the page requirements.
So, I need to start the data on Row 6.
Major cheek, but can you advise how I get the above coding to work on EVERY new sheet created
Cheers
Small Paul.

 

[Fluff]

Try

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, Split(Split(cl.Value, "(")(1), ")")(0)
            Sheets.Add(, Sheets(Sheets.Count)).name = .Item(cl.Value)
            Ws.Range("A1:H1").AutoFilter 1, cl.Value
            Ws.AutoFilter.Range.Offset(1).Copy Sheets(.Item(cl.Value)).Range("A6")
            Call [COLOR=#ff0000]Macro1[/COLOR]
         End If
      Next cl
   End With
   Ws.AutoFilterMode = False
End Sub

Change the value in red to the name of your macro

 

[Small Paul]

Hi Fluff
Absolutely bluddy brilliant! Thank you so very much.
Cheers
Small Paul.

 

[Fluff]

Glad to help & thanks for the feedback