Merge Macro Excel 2007

[Angel Rodriguez-Ayala]

Hi!

I need your help. The code to merge sheets to a master sheet runs great,
however when I copy the code to the Personal.XLSB to have the macro
available to use on every workbook I’m getting an error. The line of
code is Application.Goto DestSh.Cells(1) and the error is Method ‘Goto
of object’_Application’failed. This happen even if I create a new
workbook with 3 sheets and only a few cells with information. Can you
help me please? The macro to mail the sheet works fine using the
Personal XLSB.
Thank you,
Sub Merge_Sheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "MergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("MergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "MergeSheet"
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "MergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
Last = LastRow(DestSh)

'To copy all cells with data on the sheet
sh.UsedRange.Copy DestSh.Cells(Last + 1, "A")


'This will copy the sheet name in the H column if you want
DestSh.Cells(Last + 1, "A").Value = sh.Name

End If
Next

Application.Goto DestSh.Cells(1)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Ron de Bruin]

Hi Angel

Replace ThisWorkbook to ActiveWorkbook in the code
(a few times)