Tech Blog‎ > ‎

Merge Excel 2010 Worksheets Into One

posted Apr 18, 2012, 2:25 PM by Victor Zakharov   [ updated Apr 18, 2012, 2:27 PM ]
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)

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
  ' and save it. Simple...
    ActiveSheet.SaveAs Filename:=ActiveSheet.Name, FileFormat:=xlCSV
    counter = counter + 1
  MsgBox "All Sheets Saved.", , "Success"
  Exit Sub

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

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:

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

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