run a macro from other workbook

[kmlprtsngh]

Hi all,
I want to run a macro named findandcopyusedrange, present in workbook test.xls, from my new active workbook.
Any suggestions will be appreciated.
Kp

[johnjohns]

If both the workbooks are open, you can eaily select and run the macro. Else a better option I could find is, save the macro as addin and run from any book you open.

rgds

johnjohns

[kmlprtsngh]

Thanks for responding.
Well actually I am new to excel-vb.So,can you help me with a vb code..
Kp

[johnjohns]

This is a part of the macro I wrote for a custom menu.

Sub CreateMenu()
    Dim NewMenu As CommandBarPopup

'   Delete the menu if it already exists
    Call DeleteMenu
    
'   Find the Help Menu
    Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
    
    If HelpMenu Is Nothing Then
'       Add the menu to the end
        Set NewMenu = CommandBars(1).Controls.Add _
          (Type:=msoControlPopup, _
           temporary:=True)
    Else
'      Add the menu before Help
        Set NewMenu = CommandBars(1).Controls.Add _
          (Type:=msoControlPopup, _
           Before:=HelpMenu.Index, _
           temporary:=False)
    End If

'   Add a caption for the menu
    NewMenu.Caption = "&JohnsMenu"
    
'------------01-------------------------
'   FIRST MENU ITEM
    Set MenuItem = NewMenu.Controls.Add _
      (Type:=msoControlButton)
    With MenuItem
        .Caption = "Sum &VisibleCells"
        .OnAction = "SumVisibleCells"
        .FaceId = 233
    End With

'------------02-------------------------
'   SECOND MENU ITEM
.
.
.
.
.
.
.
.
Sub DeleteMenu()
    On Error Resume Next
    CommandBars(1).Controls("JohnsMenu").Delete
End Sub

Sub SumVisibleCells()
Dim OneCell
Dim AllVisibleCells() As String
Dim TotalVisibleCells As Integer
Dim SumFormula As String
Dim TheCellAddress As String
Dim NextCellAddress As String
On Error Resume Next
SumFormula = ""
i = 0
For Each OneCell In Selection.SpecialCells(xlCellTypeVisible)
   ReDim Preserve AllVisibleCells(i) As String
   AllVisibleCells(i) = OneCell.Address
   i = i + 1
Next
TotalVisibleCells = UBound(AllVisibleCells, 1) + 1
For i = 1 To TotalVisibleCells
   TheCellAddress = AllVisibleCells(i - 1)
   NextCellAddress = IIf(i >= TotalVisibleCells, AllVisibleCells(UBound(AllVisibleCells, 1)), AllVisibleCells(i))
   If Not IsEmpty(ActiveSheet.Range(TheCellAddress)) Then
     SumFormula = SumFormula & ActiveSheet.Range(TheCellAddress).Address(rowabsolute:=False, columnabsolute:=False) & _
                  IIf(IsEmpty(ActiveSheet.Range(NextCellAddress)), "", "+")
   End If
   If IsEmpty(ActiveSheet.Range(TheCellAddress)) Then
      ActiveSheet.Range(TheCellAddress).Formula = "=" & SumFormula
      SumFormula = ""
   End If
Next i
End Sub

I have given the macro for creating a menu and run a macro from it. you can copy this as a module to a new workbook, make the the required changes and then save as an addin. Select your addin and then run the macro 'createmenu' so that you get an additional menu in excel and you can run your macro(s) from there.

PS

CreateMenu I got from the book of John Walkenbach

rgds

johnjohns

[romperstomper]

This is a duplicate post of this one, so I'm locking it.
Note: the original post actually contains the answer too.