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
Subscribe to:
Post Comments (Atom)
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.)
ReplyDeletehi, 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
DeleteMany thanks! It really work for me.
ReplyDeleteHi! 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
ReplyDeleteI wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. freak in the sheets
ReplyDelete