Create new csv files for some sheets of my workbook

[mark84]

Hi everybody,
I have a workbook with 10 sheets named Lom mar.xlsm.
I need a vba code to create a new .csv file for the Workbook sheets named "AX", "BY", "CZ", "DU" (a csv for "AX" sheet, a csv for "BY" sheet, a csv for "CZ" sheet and a csv for "DU" sheet).
All these new csv files should be saved in the same path of the workbook (C:\Users\xxxx\Desktop\Lom mar)
Every new csv should have the same name of the original sheet (AX.csv, BY.csv, .....) and for each csv I need to delete the first row of the original sheet (header).

Could you help me please?
Thanks you so much!

Regards

Marco

 

[KOKOSEK]

Sub EachSheetAsCSV()
For Each sh In ThisWorkbook.Worksheets
    Dim F%, Rw As Range
        F = FreeFile
        Open ThisWorkbook.Path & "\" & sh.Name & ".csv" For Output As #F
    With Application
        For Each Rw In sh.UsedRange.Rows
            If Rw.Row > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
        Next
    End With
        Close #F
Next sh
End Sub

 

[mark84]

Thank you so much for your answer.
Your code is perfect but it creates a csv for each sheets of my workbook.
How can I edit that code to create a csv only for the sheets named "AX", "BY", "CZ", "DU"?

Regards
Marco

 

[mark84]

help me please

Thank you!

 

[Joe4]

One way:

Sub EachSheetAsCSV()

For Each sh In ThisWorkbook.Worksheets
    If (sh.Name = "AX") Or (sh.Name = "BY") Or (sh.Name = "CZ") Or (sh.Name = "DU") Then
        Dim F%, Rw As Range
        F = FreeFile
        Open ThisWorkbook.Path & "\" & sh.Name & ".csv" For Output As #F
        With Application
            For Each Rw In sh.UsedRange.Rows
                If Rw.Row > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
            Next
        End With
    End If
    
    Close #F
Next sh

End Sub

 

[KOKOSEK]

With longer list of worksheets this way is better, I think:

Sub EachSheetAsCSV()
Dim F%, Rw As Range
Dim V As Variant
V = Array("AX", "BY", "CZ", "DU")
For Each sh In ThisWorkbook.Worksheets
    If Not IsError(Application.Match(sh.Name, V, 0)) Then
        F = FreeFile
        Open ThisWorkbook.Path & "\" & sh.Name & ".csv" For Output As #F
        With Application
            For Each Rw In sh.UsedRange.Rows
                If Rw.Row > 1 Then Print #F, Join(.Index(Rw.Value2, 1, 0), ",")
            Next
        End With
        Close #F
    End If
Next sh
End Sub

However Joe4's codee of course works perfect as well.

 

[Joe4]

With longer list of worksheets this way is better, I think:

I agree. 4 is right around the cusp of where I would start to think about using an array too.

 

[mark84]

Thank you so much KOKOSEK and Joe4!
It works very well.

My last request:
I'm using the code with ARRAY because I could work with more than 4 sheets in the future.
I obtain the csv output but it has the format with comma between the values.
Is is possible to have a csv output that doesn't have comma as list separator?
I attach the csv output with your code and the csv output that I'd want.
Could you help me in this last point?

Regards
Marco

 

[KOKOSEK]

I do not know how did you open this CSV but it should displays each value in separate cell.

 

[mark84]

I do not know how did you open this CSV but it should displays each value in separate cell.

I opened it with Excel but all the values of each row are in a single cell, separated with comma.
What could be the problem?