VBA - Multiple sheets CSV export in the same folder

Question

I have a VBA macro which allows me to export in CSV (using the comma as separator) some sheets of my excel file, in this case, first 7 sheets. I have following problems:

1. The code allows to export first 1 - n sheets, but I would like to put the code to select sheets by name. In this case I could also export the sheet 1, called "MILANO" and the sheet 5, called "ROME".

2. I cannot find the way to save the CSV files automatically in the same folder of the source excel file. I used ActiveWorkbook.Path or ThisWorkbook.Path, but I guess I wrong something

3. I cannot export only rows of each sheet not-empty as in the CSV I see hundreds of rows with ,,,,,,,,,

Here the macro:

Sub CreateCSV()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'-----------------------------
'DECLARE AND SET VARIABLES
Dim wb1 As Workbook, ws1 As Worksheet
Dim wbname As String, I As Integer
Set wb1 = ThisWorkbook
'-----------------------------
'CYCLE THROUGH SHEETS AND MATCH UPLOAD
For I = 1 To 7
wbname = Worksheets(I).Name

'-----------------------------
'COPY SHEET INTO NEW CSV FILE
    Worksheets(I).Copy
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "/" & wbname &      "/.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    wb1.Activate
Next I
'-----------------------------
'CLEANUP
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks!

Answer 1

try this for your point 2

ActiveWorkbook.SaveAs Filename:=wb1.Path & "\" & wbname & ".csv", _

Answer 2

With regards to accessing you sheets by name you can do this,

set sh = ThisWorkBook.Sheets("MILANO")

but since you would want to loop through your sheets anyway, you need have an array with you sheet names like so,

Dim mySheets as Variant
Dim sh as WorkSheet
Dim I as Long

mySheets=Array("MILANO" , "MONACO", "ROME")

For I = 0 to UBound(mySheets)
    Set sh = ThisWorkBook.Sheets(mySheets(I))
    sh.SaveAs FileName:=ThisWorkBook.Path & "\" & mySheets(I), _
              FileFormat:=xlCSV
Next I

So you need to use the WorkSheet.SaveAs and not the WorkBook.SaveAs

as far as "I cannot export only rows of each sheet not-empty as in the CSV I see hundreds of rows with ,,,,,,,,," Perhaps you need to cleanup the Worksheet first