Can someone help? (urgently needed)

[rainx]

Hi, I have a problem here, I have a list of entries of which has different group names. I need to sort them into various files based on their group name. In a way, doing filter, However, my concern is my group names will vary every month and I also need the files to be save respectively at a place with their group name. Kinda tough, Hope someone would be able to provide some advice. Wondering if it can just filter those that are in the list into different files instead of providing a fixed list to do for all each time. Please Help!

Thanks!
<!-- / message --> <!-- sig --> __________________
Cheers
Rainx

 

[rainx]

It says object doesnt support this property or method...

Thanks!

 

[jindon]

Should be

a = .Range("a1", .Cells.SpecialCells(11)).Value

 

[rainx]

Now, there is an error saying subscript out of range...hmm..

thanks

 

[jindon]

Does your code look like this ?

Sub test()
Dim a, i As Long, ii As Long, w(), e, ws As Worksheet
Dim HeaderRow As Long
HeaderRow = 5
FilterCol=1
With ActiveSheet
    a = .Range("a1", .Cells.SpecialCells(11)).Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = HeaderRow + 1 To UBound(a,1)
        If Not IsEmpty(a(i,FilterCol)) Then
            If Not .exists(a(i,FilterCol)) Then
                ReDim w(1 To UBound(a,2), 1 To 1)
                For ii = 1 To UBound(a,2) : w(ii, 1) = a(i,ii) : Next
                .add a(i,FilterCol), w
            Else
                w = .item(a(i,FilterCol))
                ReDim Preserve w(1 To UBound(a,2), 1 To UBound(w,2) + 1)
                For ii = 1 To UBound(a,2) : w(ii, UBound(w,2)) = a(i,ii) : Next
               .item(a(i,FilterCol)) = w
            End If
        End If
    Next
    For Each e In .keys
        w = .item(e)
        On Error Resume Next
        Application.DisplayAlerts = False
        Sheets(e).Delete
        Application.DisplayAlerts = True
        On Error GoTo 0
        Set ws = Sheets.Add
        ws.Name = e
        ws.Cells(1).Resize(UBound(w,2), UBound(w,1)).Value = _
            Application.Transpose(w)
    Next
End With
End Sub

 

[rainx]

Now can le! Thanks!

But den is it possible to duplicate the 1st 5 rows of the header to those split sheets too?

Thanks!

 

[jindon]

change

        ws.Name = e
        ws.Cells(1).Resize(UBound(w,2), UBound(w,1)).Value = _
            Application.Transpose(w)

to

        ws.Name = e
        ws.Rows("1:5").Value = Me.Rows("1:5").Value
        ws.Cells(6,1).Resize(UBound(w,2), UBound(w,1)).Value = _
            Application.Transpose(w)

 

[rainx]

error with ws.Rows("1:5").Value = Me.Rows("1:5").Value

Thanks!

 

[jindon]

1) change

Dim a, i As Long, ii As Long, w(), e, ws As Worksheet
Dim HeaderRow As Long

to

Dim a, i As Long, ii As Long, w(), e, ws As Worksheet
Dim HeaderRow As Long, myHeadings

2) change

With ActiveSheet
    a = .Range("a1", .SpecialCells(11)).Value
End With

to

With ActiveSheet
    a = .Range("a1", .SpecialCells(11)).Value
    myHeadings = .Rows("1:5").Value
End With

3) change

        ws.Cells(1).Resize(UBound(w,2), UBound(w,1)).Value = _
            Application.Transpose(w)

to

        ws.Rows("1:5").Value = myHeadings
        ws.Cells(1).Resize(UBound(w,2), UBound(w,1)).Value = _
            Application.Transpose(w)

 

[rainx]

a = .Range("a1", .SpecialCells(11)).Value

Now this line gt problem...this error object doesnt support this property or method...

Thanks!

 

[jindon]

a = .Range("a1", .SpecialCells(11)).Value

Now this line gt problem...this error object doesnt support this property or method...

Thanks!

Did it again

        .Range("a1", .Cells.SpecialCells(11)).Value