We have been using XLS file for managing localization string in our projects. With some custom macro, we have generated resource files from worksheets (which corresponded to each form in our ASP.NET application). Quite comfortable for translators, but when using in distributed environment with more programmers sharing the work through SVN repos, conflicts often appeared.

So we need to split this Excel workbook into separate files (one for each worksheet), keeping macros intact.

Saving files according to sheets name was quite easy (found it here).

Sub SplitSheets()
    Dim W As Worksheet
    For Each W In Worksheets
        W.SaveAs ActiveWorkbook.PATH & "/" & W.Name
    Next W
End Sub

Second problem was removing redundant worksheets from all files. So I have written something like this (must have all files opened).

Sub RemoveSheetsFromWB()
    Dim W As Worksheet
    Dim B As Workbook
    
    For Each B In Workbooks
    For Each W In B.Worksheets
        If W.Name & ".xls" <> B.Name Then
            Application.DisplayAlerts = False
            W.Delete
            Application.DisplayAlerts = True
        End If
        If B.Worksheets.Count = 1 Then Exit For
    Next W
    Next B
End Sub