Macro to save and separate multiple worksheets as individual files in .csv format

[Ay Sticky]

Please I have multiple worksheets in a workbook saved in xlsx. The task I need the vba for, is to separate these worksheets into individual workbooks in csv format with their names corresponding to the names as used in the worksheets. It would also be great if all separated sheets could all be saved in same directory.

 

[Ay Sticky]

Again ... which line or lines are highlighted in yellow with the error ?

No line was highlighted. Only those message box were shown. No error was highlighted in the code. Perhaps I'll attach the workbook here.

 

[Ay Sticky]

A VBA demonstration « to separate these worksheets into individual workbooks text files in csv format saved in same directory » for starters :​

Sub Demo1()
    Dim F%, Ws As Worksheet, Rw As Range
        F = FreeFile
    With ActiveWorkbook
        For Each Ws In .Worksheets
            Open .Path & "\" & Ws.Name & " .csv" For Output As #F
        For Each Rw In Ws.UsedRange.Rows
            Print #F, Join(Application.Index(Rw.Value, 1, 0), ",")
        Next
            Close #F
        Next
    End With
End Sub

Job done perfectly with this! Many thanks to you. I'm grateful.

 

[Ay Sticky]

Just tried tah

As it is easily doable under Excel - maybe without importing any data to any worksheet - like without using Excel !​

The issue here is your underlevel elaboration …​

​

Did you try at least my post #7 ?​

Just tried that at post #7 and it worked. Thank you.

 

[Marc L]

because Excel wouldn't allow me to create multiple worksheets in csv format

As it is easily doable under Excel​

With an elaboration from the beginning on the source csv text file toward the final expected result it may be achieved at once …​

 

[Ay Sticky]

With an elaboration from the beginning on the source csv text file toward the final expected result it may be achieved at once …​

How please? Because with the source file being in csv format, excel warned me that I can't create multiple sheets out of it (sorry, it will create but wouldn't save) until I changed the source file format to xlsx. This is what I did and now looking for a way to convert them back individually. However, I'll be glad to know if there are options.

 

[Marc L]

Just elaborate from the source csv text file what should be achieved in order to produce the expected result …​

 

[Ay Sticky]

​

Just elaborate from the source csv text file what should be achieved in order to produce the expected result …​

The source csv text file contains only Sheet1 which has the data. Now, from Sheet1, I will do some calculations to create another data to be pasted in a newly created Sheet2. From Sheet2, I will do some calculations to create another data to be pasted in Sheet 3, and so on. After creating all these multiple sheets, using ctrl+S to save would prompt a message that all edited works would be lost because a csv file cannot save multiple worksheets. So to make Excel save my newly created worksheets, I had to SaveAs the source csv text file (containing the multiple sheets) as xlsx file.

 

[Ay Sticky]

The source csv text file contains only Sheet1 which has the data. Now, from Sheet1, I will do some calculations to create another data to be pasted in a newly created Sheet2. From Sheet2, I will do some calculations to create another data to be pasted in Sheet 3, and so on. After creating all these multiple sheets, using ctrl+S to save would prompt a message that all edited works would be lost because a csv file cannot save multiple worksheets. So to make Excel save my newly created worksheets, I had to SaveAs the source csv text file (containing the multiple sheets) as xlsx file.

In summary, my task is to open a csv text file using Excel, create multiple sheets from its only sheet, Save it (a copy of this csv text file which now contains multiple sheets), then separate the Sheets to save them individually as a csv text file.

 

[Marc L]

Two options :​

1. Stay as it is with your calculations to create each necessary worksheet and with my post #7 procedure to save each worksheet to a text file.
   
   
2. Do all at once within a single VBA procedure.

For the last one, I may help only with a complete crystal clear elaboration - so with all the necessary in order there is nothing to guess - of each step …​

 

[Ay Sticky]

Two options :​

1. Stay as it is with your calculations to create each necessary worksheet and with my post #7 procedure to save each worksheet to a text file.
   
   
2. Do all at once within a single VBA procedure.

For the last one, I may help only with a complete crystal clear elaboration - so with all the necessary in order there is nothing to guess - of each step …​

I've posted various threads for each of the task to be carried out in preparing those sheets and they've been answered. So I think I can go with option 1. However, I will still go ahead to elaborate, maybe option 2 could come out. But if my elaboration still seem unclear, then option 1 would be the only option to opt into.

The source csv text file has a single worksheet that contains 2 columns of latitudes and longitudes. Let's assume the number of these data (rows) is 100. The following tasks are to be performed on that single sheet of the source csv file.

1. Make copies of the single worksheet 98 times to make all number of worksheets to become 99 including the original worksheet (that is, numbers of worksheet equals number of rows minus one).

2. While worksheet 1 (original worksheet) remains untouched; delete row 1 in worksheet 2; delete row 1 and row 2 in worksheet 3; delete row 1, row 2, row 3 in worksheet 4; delete row 1, row2, row3, row4 in worksheet 5, this sequence of deletion goes on to last worksheet (worksheet 99).

3. Starting from the 2nd row of each worksheet, insert blank rows after every other row. That is after row 1 and row 2, a blank row should be inserted. Then after row 3, insert another blank row. After row 4, insert another blank row and so on. The idea is that this "insertion of blank row after every other row" must start after row 2, and this should process across all worksheets.

3. Fill in the blank rows with the values in each of their first row. That is values in A1:B1. For instance, the two column values in row 1 of worksheet 1 should be used to fill in all blank rows in worksheet 1. The two column values in row 1 of worksheet 2 should be used to fill in all blank rows in worksheet 2 and so on for all the worksheets.

4. Save these file with all these multiple worksheets for record purpose.

5. Separate all worksheets to be individually saved as csv text file. (This task 5 is the purpose of this present thread).