I have a worksheet with four tabs of which three are really needed. I need to have these three worksheets exported to CSV. I was told on here that since my cell lengths are over 255 characters, I will first need to copy the data. This is the order I want all this to be done..

1) User closes the worksheet {Private Sub Workbook_BeforeClose(Cancel As Boolean)}

2) Each individual worksheet is then:
a) check to see if the final exported .csv is already open
1. if so, close it
b) copy worksheet
c) the copy is then saved as tab name.csv without prompting the user

3) Save the original worksheet

On the web, I saw something about having to do something special about copying cells over 255 characters in length...

Set wsSource = ActiveSheet
ActiveSheet.Copy After:=Sheets(Sheets.Count)
Set wsNew = ActiveSheet
'--fixup for cell lengths greater than 255
wsSource.UsedRange.Copy
wsNew.Range("A1").PasteSpecial
Cells.Calculate
'-- following code from MS KB 213548 --
'Clear out the clipboard and select cell A1.
Application.CutCopyMode = False
Range("A1").Select

Do I really need to do this? Up to this point, all the code I've been writing will only export only the ActiveSheet, but will do it four times and all with the same name.

This is the code I currently have (that doesn't function properly). Don't put too much heart into it since I've done alot of fiddling:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wks As Worksheet
Dim newWks As Worksheet
Dim MyPath As String
Dim FName As String
Dim wksToCopy As Worksheet

Me.Saved = False
MyPath = ActiveWorkbook.Path

Application.DisplayAlerts = False

For Each wks In ActiveWorkbook.Worksheets

FName = wks.Name & ".csv"

' If WorkbookOpen(FName) Then
' Workbooks(FName).Close savechanges:=False
' End If

Set wksToCopy = Worksheets(wks)
wksToCopy.Copy 'copies to a new workbook
Set newWks = ActiveSheet
wksToCopy.Cells.Copy Destination:=newWks.Range("a1")

Next wks

Application.DisplayAlerts = True
ActiveWorkbook.SaveAs Filename:=MyPath & "\" & FName, FileFormat:=xlCSV
ActiveWorkbook.Close savechanges:=False

End Sub

Any help would be greatly appreciated! This is for Excel 2003.