Export colums as individual csv files

[steffen82]

Hi Could someone advise on how you can export each column to an individual csc or tab file using the header as the file name please.

i.e.

Street 1	Street 2	Street 3
1	1C	1 MOORGROVE HOUSE
2	3	2 MOORGROVE HOUSE
3	5	3 MOORGROVE HOUSE
4	7	4 MOORGROVE HOUSE
5	9	5 MOORGROVE HOUSE
6	11	6 MOORGROVE HOUSE

<tbody>
</tbody><colgroup><col><col><col></colgroup>

Exported to these files

Filename = Street 1.txt or .csv
File contents:

1

2

3

4

5

6

<tbody>
</tbody><colgroup><col></colgroup>

File name = Street 2.txt
File contents:

1C

3

5

7

9

11

<tbody>
</tbody><colgroup><col></colgroup>

File name = Street 3.txt

1 MOORGROVE HOUSE

2 MOORGROVE HOUSE

3 MOORGROVE HOUSE

4 MOORGROVE HOUSE

5 MOORGROVE HOUSE

6 MOORGROVE HOUSE

<tbody>
</tbody><colgroup><col></colgroup>

 

[John_w]

Try this, changing the folder path as required.

Public Sub Export_Each_Column_As_CSV_File()

    Dim lastCol As Long, lastRow As Long, c As Long
    
    With ActiveSheet
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For c = 1 To lastCol
            lastRow = .Cells(Rows.Count, c).End(xlUp).Row
            Open "C:\path\to\folder\" & .Cells(1, c).Value & ".csv" For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
            Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , Join(Application.Transpose(.Cells(2, c).Resize(lastRow - 1).Value), vbCrLf)
            Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        Next
    End With
    
End Sub

 

[steffen82]

Hi
Probably a silly question but is that a macro?

 

[John_w]

Yes! http://www.contextures.com/xlvba01.html#videoreg explains where and how to insert the code.