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]

Fluff
Please come to rescue again as this is really odd! The whole thing was working perfectly yesterday and this morning. I now get an error on line 6 below (I have separated it below just for ease):

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")

What seems to happen is the last worksheet created does not have column A deleted. Apart from that, it seems fine.
Have I corrupted somehow?
Cheers
Small Paul.

 

[Fluff]

Do you have any cells in col A that don't have a value in ()

 

[Small Paul]

No, all the cells are empty.
Macro inserts a new column A, copy/paste column I into A. This works on all of the new sheets created, except for the final one which has all the columns shifted 1 to the right i.e. what should be in column A is in column B

 

[Fluff]

Macro inserts a new column A, copy/paste column I into A

There is no code on this thread that does what you describe.
Also if col A is empty, then the code I supplied won't work as it's looking a col A for the values to filter & name the new sheets.

 

[Small Paul]

    ChDir "Z:\"    ChDir "Z:\Orders"
    Workbooks.Open Filename:= _
        "Z:\Orders\De Vere Daily Update Paul Test.xlsm" _
        , UpdateLinks:=0


    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste
   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 Macro18
         End If
      Next cl
   End With
   Ws.AutoFilterMode = False
End Sub

 

[Fluff]

If you are getting an error on this line

.Add cl.Value, Split(Split(cl.Value, "(")(1), ")")(0)

It's probably that you have a cell in col A that does not include any brackets

 

[Small Paul]

What is the actually instructing?

 

[Fluff]

That line is looking for the value inside the brackets, to use as the sheet name.

 

[Small Paul]

This is SO frustrating!
The coding was working fine - until a colleague starting using it!!
Using the following code:

    ChDir "Z:\"    ChDir "Z:\Orders"
    Workbooks.Open Filename:= _
        "Z:\Orders\De Vere Daily Update Paul Test.xlsm" _
        , UpdateLinks:=0


    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste


   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 De_Vere_Daily_Update_Macro_2
         End If
      Next cl
   End With
   Ws.AutoFilterMode = False


End Sub

I am now getting "Runtime Error 1004 - Autofilter method of range class failed" at the line:

        Ws.Range("A1:H1").AutoFilter 1, cl.Value

Can somebody shed some light on this please.
Cheers
Small Paul.

 

[Fluff]

Do you have any data in A1:H1 on the activesheet?
Also is your data in a table?