Merge Macro Excel 2007

  • Thread starter Angel Rodriguez-Ayala
  • Start date


Angel Rodriguez-Ayala


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
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

Application.Goto DestSh.Cells(1)

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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question