How to run a macro in sheet1 from sheet2

Question

I can't figure out what to change to be able to run this macro from another sheet. Any idea ?

• This macro is stored in sheet "Mail"

• I want to run the macro from Sheet2 via a button

Is it possible or do I have to change the macro fundamentally?

    Sub DynamicRange()
    'Find the last row in the range then make the slection
    '-----------------------------------------------------------------------
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim StartCell As Range
    Dim Sendrng As Range
    Dim strbody As String

    '----------------------------------------------------------------
    If IsEmpty(Range("B26").Value) = True Then
    ThisWorkbook.Sheets("Mail").Range("B2:K26").Select
    Else        
    '----------------------------------------------------------------

    Set sht = Worksheets("Mail")
    Set StartCell = Range("B2")

    'Refresh UsedRange
    Worksheets("Mail").UsedRange

    'Find Last Row
    LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, 
    SearchDirection:=xlPrevious).Row

    'Select Range
    sht.Range("B2:K" & LastRow).Select

    End If

    '-----------------------------------------------------
    'Mail codes starts here
    On Error GoTo StopMacro

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

    'Note: if the selection is one cell it will send the whole worksheet
     Set Sendrng = Selection

    'Create the mail and send it
    With Sendrng

    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope

        With .Item
            .To = "here@myour.mail"
            .CC = ""
            .BCC = ""
            .Subject = ThisWorkbook.Sheets("Mail").Range("O4").Value
            .Importance = 2
            .ReadReceiptRequested = True
            .Send
        End With

    End With
    End With

    StopMacro:
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

    End Sub

Answer 1

Explanation

Always specify a Range() or Cells() with a worksheet like sht.Range().
Of course sht has to be set to your desired worksheet first:

Set sht = ThisWorkbook.Worksheets("Mail")

Also try to avoid .Select instead set the desired range to a variable Set SendRng = sht.Range("B2:K26") and use this later.

Apparently MailEnvelope really sends the selected range from the active sheet :( so this time we have a real use case for .Select and .Activate where we cannot avoid it (at least I didn't find one).

This way your code gets independent from the active worksheet and any selection, so you can run it whatever sheet or cell is selected.

---

I tidied up your code a bit. Save this into a module and it should run from any worksheet.

Note: I removed unused variables.

Option Explicit

Sub DynamicRange()
    Dim sht As Worksheet, actSht As Worksheet
    Dim LastRow As Long
    Dim SendRng As Range

    Set sht = ThisWorkbook.Worksheets("Mail")
    
    'Find the last row in the range then make the slection
    '-----------------------------------------------------------------------
    If IsEmpty(sht.Range("B26").Value) = True Then
        Set SendRng = sht.Range("B2:K26")
    Else        
        'Find Last Row
        LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
        'Select Range
        Set SendRng = sht.Range("B2:K" & LastRow)
    End If

    '-----------------------------------------------------
    'Mail codes starts here
    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set actSht = ActiveSheet 'remember active sheet

    'Create the mail and send it
    With SendRng 
        .Parent.Activate 'we need to activate the mail sheet
        .Select 'we need to select the range which should be in the email

        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope.Item
            .To = "here@myour.mail"
            .CC = ""
            .BCC = ""
            .Subject = sht.Range("O4").Value
            .Importance = 2
            .ReadReceiptRequested = True
            .Send
        End With
    End With

StopMacro:
    actSht.Select 'jump back to the sheet we came from
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False
End Sub

Answer 2

Implicit references to ActiveSheet

If the macro is implemented in the code-behind of sheet "Mail", then anywhere you query the Worksheets collection to fetch an object reference for that sheet, you can replace with Me, which refers to the current instance of a class module - and yes, worksheet modules are class modules. Like for userforms, VBA declares a globally-scoped object for them - you can determine the identifier to use for that object in the Properties toolwindow (F4), by changing the (Name) property of the worksheet. So you can name your sheet MailSheet and use that in code:

Debug.Print MailSheet.Name

Now, whenever you use an unqualified Range call (or Rows, or Cells, or Names, or Columns), you're implicitly referring to whatever worksheet is currently active, which may or may not be the worksheet you intended to work with. This regularly causes run-time errors and you will find dozens upon dozens of questions on this site where this is the root cause - always qualify Range calls with a proper worksheet object and you won't have that problem.

I'm heavily involved in Rubberduck, an add-in for the VBE (VBA's IDE/editor) that can locate all instances of implicit references to the active worksheet, and a couple of other common beginner (and some not-so-beginner) mistakes. You may want to give it a try, it's free and open-source.

---

Calling the procedure

If the procedure were implemented as a Public Sub in a standard module (.bas) instead of a worksheet's code-behind, then you could call it like this, from anywhere in the project:

DynamicRange

Note that that's a rather poor name for a procedure; procedures do something, their names should start with a verb, and the name should convey what it's doing.

If you don't want to move the procedure to a standard procedural module, then you can always leave it a public member of the worksheet module (if Public isn't specified then it's implicit; module members in VBA are always Public by default) and call it like this:

MailSheet.DynamicRange

Assuming you gave the "Mail" sheet the name MailSheet in the properties toolwindow, as described above.

---

Parameterizing the procedure

If you want your procedure to work against any specified worksheet, then you need to parameterize it. The signature becomes something like this:

Public Sub DynamicRange(ByVal sht As Worksheet)

So when you call that procedure, you can supply a Worksheet object:

DynamicRange MailSheet

or

MailSheet.DynamicRange Sheet2

For this to work though, you need to remove the local declaration and assignment for your sht variable, and have the code work against the specified worksheet instead.