Wednesday, February 3, 2010

Macro to combine multiple sheets of EXCEL data in stata

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

4 comments:

  1. Note to all: this converts multiple EXCEL sheets into one EXCEL sheet which can then be STATransfered or copy, cut, paste into another program (e.g. SAS, STATA, etc.)

    ReplyDelete
    Replies
    1. hi, i copied the txt provided to MSVB and executd it. Hoewever, i recieve a blank "combined" sheet. There are around 89 sheets with data. Is there something to update before executing? Please advise. Thank you in advanced! AA

      Delete
  2. Many thanks! It really work for me.

    ReplyDelete
  3. Hi! This was really useful - can you post a macro for then changing/updating information on the master file that will also mirror the change on the source file? Thanks so much

    ReplyDelete