Merge Excel 2010 worksheets into one

Step 1. Save each worksheet into an individual file. You will need Developer tab enabled/visible to use the code below - File/Options/Customize Ribbon/Check ‘Developer’ tab on the right.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
Option Explicit
Sub mysaver()
  Dim counter As Integer
  counter = 1
  ' counter is for the number of sheets in the workbook
  Do While counter <= Worksheets.Count
  ' Worksheets.Count represents the total number of sheets in the workbook
    On Error GoTo ErrorHandler
  ' go to the nominated sheet
    Worksheets(counter).Activate
  ' and save it. Simple...
    ActiveSheet.SaveAs Filename:=ActiveSheet.Name, FileFormat:=xlCSV
    counter = counter + 1
  Loop
  MsgBox "All Sheets Saved.", , "Success"
  Exit Sub

ErrorHandler:
  MsgBox "Error during save - Caution!", vbCritical, "Save Errors"
  Exit Sub
End Sub

thanks to http://www.accountingweb.co.uk/anyanswers/macro-exporting-50-worksheets-csv-one-pass for this code.

Note: files will be generated either under My documents, if the document was unsaved, or within the same path as the document itself, if saved.

Step 2. Manually copy all generated files into some folder.
Step 3. Run Powershell, navigate to that folder (i.e. cd) and run the following command to merge all files into one:

1
get-childitem | foreach { add-content -value (get-content $_) -path mergefile.csv }

Here credit goes to http://dev.woganmay.com/2011/12/27/merge-multiple-files-using-powershell/ (resource is no longer available, but I will keep it for records).

Step 4. Open the resulting CSV file, and save as XLS if you need to.

Merge Excel 2010 worksheets into one

See Also