Export multiple (specific) worksheets to CSV files in a specified directory

[reghyh]

I'm new to VBA. I'm trying to do the following but the code isn't quite working as expected -

1. Export/Copy particular sheets in the workbook (any sheet name that contains "Upload") to a particular file directory.
2. I don't want these worksheet names to change nor the workbook name to change.
3. The file-name is consistent for each worksheet, so it would be okay to replace the files in the directory whenever I run the macro. Its okay to have a dialog box that asks if I'm sure I want to replace each of the files.
4. I don't want the newly created CSVs or any other file to open.

Sub COPYSelectedSheetsToCSV()
'
'
Sheets("Moo Upload").Select
Sheets("Moo Upload").Name = "Moo Upload"
ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Moo Upload.csv", _
FileFormat:=xlCSV, CreateBackup:=False
Sheets("Dodo Upload").Select
ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Dodo Upload.csv", _
FileFormat:=xlCSV, CreateBackup:=False
Sheets("Lulu Upload").Select
ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Lulu Upload.csv", _
FileFormat:=xlCSV, CreateBackup:=False
Sheets("Ahhh Upload").Select
ActiveWorkbook.SaveAs Filename:="/Users/reginaho/Desktop/Upload/Ahhh Upload.csv", _
FileFormat:=xlCSV, CreateBackup:=False

End Sub

 

[ImranMalek]

Here you go ,

Sub COPYSelectedSheetsToCSV()


Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False


Sheets("Moo Upload").SaveAs Environ("USERPROFILE") & "\Desktop\Upload\Moo Upload.csv", xlCSV
Sheets("Dodo Upload").SaveAs Environ("USERPROFILE") & "\Desktop\Upload\Dodo Upload.csv", xlCSV
Sheets("Lulu Upload").SaveAs Environ("USERPROFILE") & "\Desktop\Upload\Lulu Upload", xlCSV
Sheets("Ahhh Upload").SaveAs Environ("USERPROFILE") & "\Desktop\Upload\Ahhh Upload", xlCSV


Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

Hope it helps !