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
    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

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.
Comments