Saving of data into separate file using different choice and into different folder

Originally Posted by LeoTaxi

Something wrong with file, dont know what so i create a new one, with small modification in code

Sub test()
Dim lr As Long, arr As Variant, x As Long
Application.ScreenUpdating = False
With Sheets("Request")
    arr = .Range("A1", "C" & .Range("A" & .Rows.Count).End(xlUp).Row)
    For x = 1 To UBound(arr)
        If arr(x, 3) = "" Then arr(x, 3) = arr(x - 1, 3)
    Next
End With
With Sheets("Sheet1")
    colp = Application.Match("Product", .Range("1:1"), 0)
    colc = Application.Match("Customer Code", .Range("1:1"), 0)
    colt = Application.Match("Type", .Range("1:1"), 0)
    If .AutoFilterMode Then .AutoFilter.ShowAllData
    For x = 1 To UBound(arr)
        Select Case arr(x, 3)
            Case Is = "C"
                .Range("A1").CurrentRegion.AutoFilter field:=colc, Criteria1:=arr(x, 1)
                With .Range("A1").CurrentRegion
                    .Offset(1).Copy Sheets("Help").Range("A1")
                End With
                With Sheets("Help")
                    lr = .Range("A" & .Rows.Count).End(xlUp).Row
                    .Sort.SortFields.Add2 Key:=Range(Cells(1, colp), Cells(lr, colp)) _
                    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                With Sheets("Help").Sort
                    .SetRange Range("A1").CurrentRegion
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                Sheets("Help").Copy
                ActiveWorkbook.SaveAs arr(x, 2) & "\" & Range("A1").Value & ".xlsx"
                ActiveWorkbook.Close True
                Sheets("Help").Range("A1").CurrentRegion.ClearContents
            Case Is = "P"
                .Range("A1").CurrentRegion.AutoFilter field:=colp, Criteria1:=arr(x, 1)
                With .Range("A1").CurrentRegion
                    .Offset(1).Copy Sheets("Help").Range("A1")
                End With
                With Sheets("Help")
                    lr = .Range("A" & .Rows.Count).End(xlUp).Row
                    .Sort.SortFields.Add2 Key:=Range(Cells(1, colc), Cells(lr, colc)) _
                    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                With Sheets("Help").Sort
                    .SetRange Range("A1").CurrentRegion
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                Sheets("Help").Copy
                ActiveWorkbook.SaveAs arr(x, 2) & "\" & Range("B1").Value & ".xlsx"
                ActiveWorkbook.Close True
                Sheets("Help").Range("A1").CurrentRegion.ClearContents
            Case Is = "T"
                .Range("A1").CurrentRegion.AutoFilter field:=colt, Criteria1:=arr(x, 1)
                With .Range("A1").CurrentRegion
                    .Offset(1).Copy Sheets("Help").Range("A1")
                End With
                With Sheets("Help")
                    lr = .Range("A" & .Rows.Count).End(xlUp).Row
                    .Sort.SortFields.Add2 Key:=Range(Cells(1, colc), Cells(lr, colc)) _
                    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .Sort.SortFields.Add2 Key:=Range(Cells(1, colp), Cells(lr, colp)) _
                    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                End With
                With Sheets("Help").Sort
                    .SetRange Range("A1").CurrentRegion
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
                Sheets("Help").Copy
                ActiveWorkbook.SaveAs arr(x, 2) & "\" & Range("C1").Value & ".xlsx"
                ActiveWorkbook.Close True
                Sheets("Help").Range("A1").CurrentRegion.ClearContents
        End Select
        .AutoFilter.ShowAllData
    Next
End With
End Sub

Dear LeoTaxi,

I think I know what causes the error.

My sample file has only 4 columns but my actual data file the columns is until K where some of the columns after F will be blank. But from column A to F will be filled with data.

The next problem will be, in column A, I will have files with "\" as separators, which is not "acceptable" to be named as file name. On top of this naming problem, the other problem will be the decision to "identify" the search.

Meaning, I will have account code like this:

KFC - Level 1
California - Level 2
Alhambra - Level 3

Combine together will be KFC\California\Alhambra.

I can have:
KFC\California\Alhambra
KFC\California\Chico
KFC\FLORIDA\BARTOW
KFC\FLORIDA\CAPECOR

So, when I input into the "request" column A, when I key in KFC, it will gives error.

Is it possible to extract out the following if I enter into request column A:

KFC (to extract all information with Level 1 account code KFC, including all of California and Florida?)

KFC\FLORIDA (to extract all information with Level 1 account code KFC and all Level 2 code Florida?)

KFC\California\Chico (to extract only Chico?)

And lastly, the naming of the file, to replace "\" with "-" or "_"?

If this is not possible, I shall close this post.