The only portion that doesn't work within excel is the clean up.

I like to verify first, but if you want that cleaned up:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim myTempFolder As String
Dim myFileName As String
Dim iCtr As Long

'Dim FSO As Scripting.FileSystemObject
Dim FSO As Object

'Set FSO = New Scripting.FileSystemObject
Set FSO = CreateObject("scripting.filesystemobject")

myTempFolder = "C:\" & Format(Now, "yyyymmdd_hhmmss")

On Error Resume Next
MkDir myTempFolder
If Err.Number < 0 Then
MsgBox "oh, oh"
Exit Sub
End If

iCtr = 0
For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = "sheet1", "sheet2" 'do nothing
Case Else
wks.Copy 'copies to a new workbook
With ActiveSheet
iCtr = iCtr + 1
myFileName = myTempFolder & "\" & Format(iCtr, "000000")
.Parent.SaveAs Filename:=myFileName, _
FileFormat:=xlCSV
.Parent.Close savechanges:=False
End With
End Select
Next wks

Shell Environ("comspec") & " /k copy /b " & myTempFolder & "\*.csv " _
& myTempFolder & "\All.txt", vbNormalFocus
'/k keeps the DOS window open (nice for testing)
'/c closes the DOS window

Application.Wait Time:=Now + Time(0, 0, 5)
'a little time for the copy command to finish

FSO.DeleteFile filespec:=myTempFolder & "\*.csv"

Name myTempFolder & "\all.txt" As myTempFolder & "\all.csv"

End Sub

<<snipped