I am working on and add-in that deletes empty rows and columns for
sorting and subtotalling. The data is generated from a Crystal Report.


I manually run the code to generate the menu item and then am hoping
that the procedure will execute on any new sheet.

I am having two problems with the Add-In
A) When I click on the menu item, I get an error that the macro can't
be found - "The macro 'ClearSheet.xla!CleanSheet' cannot be found"
B) Executing the the code in the Add-In doesn't seem to delete the rows
and columns in the new workbook. I have to copy the code over to the
new workbook and then run the code from there.

Here is the code:

Public Sub CreateMenu()
Dim HelpMenu As CommandBarControl
Dim NewMenu As CommandBarPopup
Dim MenuItem As CommandBarControl
Dim SubMenuItem As CommandBarButton

Call DeleteMenu

Set HelpMenu = Application.CommandBars(1).FindControl(ID:=30010)

'If Help Menu doesn't exist add to end of menu items
If HelpMenu Is Nothing Then
Set NewMenu =
Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup,
temporary:=False)
Else
Set NewMenu =
Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup,
Befo=HelpMenu.Index, temporary:=False)
End If

NewMenu.Caption = "Reporting"

'First Menu Item
Set MenuItem = NewMenu.Controls.Add(Type:=msoControlButton)

With MenuItem
..Caption = "Gross &Margin by Part ID"
..OnAction = "CleanSheet"
End With

End Sub

Sub DeleteMenu()
On Error Resume Next
CommandBars(1).Controls("Reporting").Delete
End Sub

Public Sub CleanSheet()
Deleted for brevity
(Code that deletes empty rows and columns)
End Sub