如何从sheet2在sheet1中运行宏 [英] How to run a macro in sheet1 from sheet2

问题描述

我无法弄清楚要更改的内容，以便能够从另一张工作表中运行此宏。任何想法？

• 此宏存储在表格邮件




• 我想通过按钮从Sheet2运行宏

是可能还是有从根本上改变宏观？

  Sub DynamicRange（）
'查找范围中的最后一行，然后使slection 
' -------------------------------------------------- -------------------- 
 Dim sht As Worksheet 
 Dim LastRow As Long 
 Dim LastColumn As Long 
 Dim StartCell作为范围
 Dim Sendrng As Range 
 Dim strbody As String 
 
'----------------------- ----------------------------------------- 
如果IsEmpty（Range（ B26）Value）= True Then 
 ThisWorkbook.Sheets（Mail）。Range（B2：K26）。选择
 Else 
'------- -------------------------------------------------- ------- 
 
设置sht =工作表（邮件）
设置StartCell =范围（B2）
 
'刷新UsedRange 
工作表（Mail）。UsedRange 
 
'查找最后一行
 LastRow = sht.Cells.Find（*，SearchOrder：= xlByRows，
 SearchDirection = xlPrevious）.Row 
 
'选择Rang e 
 sht.Range（B2：K& LastRow）。选择
 
如果
 
'--------------------------- -------------------------- 
'邮件代码从这里开始
错误GoTo StopMacro 
 
应用程序
 .ScreenUpdating = False 
 .EnableEvents = False 
结束
 
'注意：如果选择是一个单元格，它将发送整个工作表
设置Sendrng =选择
 
'创建邮件并发送
与Sendrng 
 
 ActiveWorkbook.EnvelopeVisible = True 
 With .Parent.MailEnvelope $ 
 $ b $ .Sheets（Mail）。Range（O4）。value 
 .Importance = 2 
 .ReadReceiptRequested = True 
 .Send 
 End with 
 
结束
结束
 
 StopMacro：
应用程序
 .Screen更新= True 
 .EnableEvents = True 
结束
 ActiveWorkbook.EnvelopeVisible = False 
 
 End Sub 
  

解决方案

说明

始终指定一个使用工作表如 sht.Range（）的范围（）或 Cells（）

当然 sht 必须首先设置为您想要的工作表：

 设置sht = ThisWorkbook.Worksheets（邮件）
  

还尝试避免。选择，将所需的范围设置为变量 Set SendRng = sht.Range（B2：K26）并且稍后再使用。

显然MailEnvelope真的从活动表单发送所选范围:(所以这次我们有一个真正的用例。选择和 .Activate 我们无法避免（至少我没有找到它）。

这样您的代码独立于活动工作表和任何选择，所以您可以运行它所选择的工作表或单元格。

---

我已经整理了你的代码了。将其保存到模块中，它应该从任何工作表运行。

注意：我删除了未使用的变量。

  Option Explicit 
 
 Sub DynamicRange（）
 Dim sht As Worksheet，actSht As Worksheet 
 Dim LastRow As Long 
 Dim SendRng As Range 
 
 Set sht = ThisWorkbook.Worksheets（Mail）
 
'查找范围中的最后一行，然后选择
'----------- -------------------------------------------------- ---------- 
如果IsEmpty（sht.Range（B26）。值）= True然后
设置SendRng = sht.Range（B2：K26）
 Else 
'查找最后一行
 LastRow = sht.Cells.Find（*，SearchOrder = = xlByRows，SearchDirection：= xlPrevious）.Row 
 
'选择范围
 Set SendRng = sht.Range（B2：K& LastRow）
 End If 
 
'--------------- -------------------------------------- 
'邮件代码从这里开始
错误GoTo StopMacro 
 
应用程序
 .ScreenUpdating = False 
 .EnableEvents = False 
结束
设置actSht = ActiveSheet记住活动表
 
'创建邮件并发送
带SendRng 
 .Parent.Activate'我们需要激活邮件表
。选择'我们需要选择应该在电子邮件中的范围
 
 ActiveWorkbook.EnvelopeVisible = True 
与.Parent.MailEnvelope.Item 
 .To =[e-mail]
 .CC =
 .BCC =
 .Subject = sht .Range（O4）。值
 .Importance = 2 
 .ReadReceiptRequested = True 
。发送
结束
结束
 
 StopMacro：
 actSht.Select'跳回我们从
的表单与应用程序
 .ScreenUpdating = True 
 .EnableEvents = True 
结束
 ActiveWorkbook.EnvelopeVisible = False 
 E nd Sub 
  

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 = "[e-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

解决方案

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 = "[e-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

这篇关于如何从sheet2在sheet1中运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！