Skip to content

Instantly share code, notes, and snippets.

@dude333

dude333/Combine.vbs

Created March 31, 2020 17:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dude333/0a9263c67aae2ee982ad102dec0ef7e3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dude333/0a9263c67aae2ee982ad102dec0ef7e3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. dude333 created this gist Mar 31, 2020.
    27 changes: 27 additions & 0 deletions Combine.vbs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,27 @@
    ' Source: https://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
    Sub Combine()
    Dim J As Integer

    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
    Sheets(J).Activate ' make the sheet active
    Range("A1").Select
    Selection.CurrentRegion.Select ' select all cells in this sheets

    ' select all lines except title
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

    ' copy cells selected in the new sheet on last line
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
    End Sub