It's not a problem with the wks.copy line.

It's a problem with the "with activesheet" line.

The activesheet is a property that can belong to a window, a workbook, or the
application.

Since this code is behind the ThisWorkbook module, and it's not qualified by
anything, excel/VBA assumes that it belongs to the thing holding the code--in
this case, it's the workbook that's closing.

Try this:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks As Worksheet
Dim MyPath As String

MyPath = ActiveWorkbook.Path
For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'copies to a new workbook

With Application.ActiveSheet
Application.DisplayAlerts = False
.Parent.SaveAs Filename:=MyPath & "\" & .Name, _
FileFormat:=xlCSV
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
Next wks
End Sub


mainemike wrote:

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!

--
mainemike

--

Dave Peterson