Macro to export sheets as CSV

[howard]

i have a workbook with several sheet. I need a macro to save each of these sheets from sheet 4 onwards as a CSV file . It must be saved with the respective sheet names in folder C:\my documents containing the current month and year for eg if sheets are ETYB, RREZ etc save as ETYB April 2022.csv, RREZ April 2022 etc


Your assistance is most appreciated

 

[Joe4]

Try something like this (the file is automatically closed at the end, so as not to mess up the structure of the file you have):

Sub CreateCSVFiles()

    Dim s As Long
    Dim myDir As String
    Dim fName As String
    
'   Save workbook before starting
    ActiveWorkbook.Save
    
'   Designate directory to save files to
    myDir = "C:\My Documents\"
    
'   Loop through all sheets starting with the 4th
    For s = 4 To Worksheets.Count
        Sheets(s).Activate
'       Build file name
        fName = Sheets(s).Name & Format(Date, " mmmm yyyy") & ".csv"
'       Save as CSV file
        ActiveWorkbook.SaveAs Filename:=myDir & fName, FileFormat:=xlCSV, CreateBackup:=False
    Next s
    
'   Close workbook without saving
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
        
End Sub

 

[Micron]

Won't the index of the sheet get messed up if you move them around? That is, 4 could become 1?
I was thinking of using the codename property, assuming Sheet4 is the code name.

 

[howard]

Thanks for your code Joe -it works perfectly

 

[Joe4]

You are welcome.