Coping with duplicates being created by code splitting worksheets into separate files

[Kenneth Hobs]

Fully defining your goals is usually the best way to get help.

I notice that you don't post often in this forum nor Excelforum.com. I also see that you give up and put a message that you consider the thread closed. Let the moderators do that. Some forums let you mark a thread SOLVED but if you do, say no solution was found but even then, I suggest doing that as a last resort though do marked solved when it is. IF you don't see a response in say 2 days, then put a short reply and say bump, or still need help or send a Private Message (PM) to some that were helping but may have forgotten. I know that in one here, I was going to give more help but I am busy and forgot about the thread and I do help others in this forum and others. That is why cross-posting is generally, not a good idea.

I have not gotten into this thead because of all the fine help that you received at both forums. Even so, sometimes a fresh set of eyes can help.

I see two approaches to solve your latest request to just save the worksheets only as separate files. The first method, involves saving to a CSV file. I will post two ways to do that. The second approach is to Copy each file, open it, and then delete all but the sheet of interest. This saves your formats but does take a bit more work and is a bit slower. I think this last method that I did was one chosen for a top 100 tips publication by MrExcel.

' http://www.vbaexpress.com/forum/showthread.php?t=42769
Public Sub SaveAllShtCSV()
  
    Dim wbThis As Workbook, i As Integer
    Dim colDelimiter As String
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    colDelimiter = Application.International(xlColumnSeparator)
    
    'Application.International(xlColumnSeparator) = ";"
    Set wbThis = ThisWorkbook
    For i = 1 To wbThis.Sheets.Count
        wbThis.Sheets(i).Copy
        With ActiveWorkbook
            .SaveAs FileName:=ThisWorkbook.Path & "\" & wbThis.Sheets(i).Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False 'Change path to suit
            'xlCSV does same as xlCSVWindows
            .Close
        End With
    Next i
     
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    'Application.International(xlColumnSeparator) = colDelimiter
     
End Sub

' http://www.vbaexpress.com/forum/showthread.php?t=42769
Sub ExportSheets()
  Dim ws As Worksheet, exportPath As String, s As String
  exportPath = ThisWorkbook.Path & "\"
  For Each ws In Worksheets
    With ws
    ' http://www.cpearson.com/excel/ImpText.aspx
      ExportToTextFile ThisWorkbook.Path & "\" & .Name & ".txt" _
        , ";", False, False
    End With
  Next ws
End Sub