I need help writing some VBA code. Here's what I'm trying to do...

When a user closes an Excel spreadsheet (results.xls), I want to use Workbook/BeforeClose to export all the contents of all worksheets, as CSVs, as tab name.csv, to the directory that the original spreadsheet was opened from. I do not want any user interaction.

What I have so far is...

' Start of coding
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
Dim newWks As Worksheet
Dim MyPath As String
MyPath = ActiveWorkbook.Path
For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:=MyPath & "\" & .Name, FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
Next wks
End Sub
' End of coding

Here are my problems so far...
1) It only saves one out of the four worksheets.
2) If the .csv file exists, it prompts to overwrite. I want it to just write over without the prompt.
3) The user ends up with a new workbook that has only the first worksheet. I'm assuming this is a result of the "wks.Copy" not 'cleaning up' correctly.


If anyone could help debug this, or give me suggestions on how to streamline the code, I would greatly appreciate it!