Split Excel file into multiple Excel files based on specific column value

Hello,

it's been a while not visiting this forum. Hope everyone is good

Anyway, i have i macro to split csv file into multiple csv files based on spesific column value.

here's the code:

Sub Splitsinglecsvintomultiplecsv()
     
    Dim FF As Integer
    Dim wLine As String
    Dim saveLocation As String
    Dim cID As String
    Dim lRow As Long
     
    saveLocation = "D:\" '//Change as required, make sure you have a trailing "\"
    
    Cells(2, 5).Activate
     
    Do
        FF = FreeFile
        cID = ActiveCell.Value
        Open saveLocation & cID & ".txt" For Output As #FF
        Print #FF, "No,data1,data2,data3,data4,data5"

        While ActiveCell.Value = cID
            lRow = ActiveCell.Row
            Print #FF, Cells(lRow, 1) & "," & Cells(lRow, 2) & "," & Cells(lRow, 3) & "," & Cells(lRow, 4) & "," & Cells(lRow, 5) & "," & Cells(lRow, 6)
            ActiveCell.Offset(1, 0).Activate
        Wend
        Close #FF
        Name saveLocation & cID & ".txt" As saveLocation & cID & ".csv"
    Loop Until ActiveCell.Value = ""
     
End Sub

it work to split file based on specific column value that i have set. How ever there is error on result each file.
the cell contain several commas split into multiple column causing not match with the header.

appreciate if anyone can check the code.

also i want to save the result as .xlsx as current result is in .csv format.


Thank you.

Originally Posted by qiyusi

... i have i macro to split csv file into multiple csv files based on spesific column value ...
... also i want to save the result as .xlsx as current result is in .csv format ...

This macro does not split the "csv" file into many other "csv" files.
This macro creates many "csv" files from the excel sheet content based on the data from the "E" column cells.

What is the "input material" for the division and what should be the "output material" of the division ?
a) one excel sheet to many csv files ?
b) one csv file (txt file) to many csv files (txt files) ?
c) one excel sheet to many csv files and many xls files ?

Give us maybe an example of files - input/output file - the data does not have to be true.

Hello,

Really sorry for very late respond.

Attached is the Example file with the explanation.


Thank you.