Combine worksheets

[sandy armstrong]

I found this code online i really need to merge all me file together can some tell me were i going wrong with this.

1. Sub CopyRangeFromMultiWorksheets()
2. Dim sheet As Worksheet
3. Dim DestSheet As Worksheet
4. Dim Last As Long
5. Dim CopyRng As Range
   
6. With Application
7. .ScreenUpdating = False
8. .EnableEvents = False
9. End With
   
10. ' Delete the summary sheet if it exists.
11. Application.DisplayAlerts = False
12. On Error Resume Next
13. ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
14. On Error GoTo 0
15. Application.DisplayAlerts = True
    
16. ' Add a new summary worksheet.
17. Set DestSh = ActiveWorkbook.Worksheets.Add
18. DestSh.Name = "RDBMergeSheet"
    
19. ' Loop through all worksheets and copy the data to the
20. ' summary worksheet.
21. For Each sh In ActiveWorkbook.Worksheets
22. If sh.Name <> DestSh.Name Then
    
23. ' Find the last row with data on the summary worksheet.
24. Last = LastRow(DestSheet)
    
25. ' Specify the range to place the data.
26. Set CopyRng = sh.Range("A1:G1")
    
27. ' Test to see whether there are enough rows in the summary
28. ' worksheet to copy all the data.
29. If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
30. MsgBox "There are not enough rows in the " & _
31. "summary worksheet to place the data."
32. GoTo ExitTheSub
33. End If
    
34. ' This statement copies values and formats from each
35. ' worksheet.
36. CopyRng.Copy
37. With DestSh.Cells(Last + 1, "A")
38. .PasteSpecial xlPasteValues
39. .PasteSpecial xlPasteFormats
40. Application.CutCopyMode = False
41. End With
    
42. ' Optional: This statement will copy the sheet
43. ' name in the H column.
44. DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name
    
45. End If
46. Next
    
47. ExitTheSub:
    
48. Application.Goto DestSh.Cells(1)
    
49. ' AutoFit the column width in the summary sheet.
50. DestSh.Columns.AutoFit
    
51. With Application
52. .ScreenUpdating = True
53. .EnableEvents = True
54. End With
55. End Sub