时间戳

首页 > 解决方案 > 宏仅在活动工作表中启动

excel - 宏仅在活动工作表中启动

问题描述

我是 excel VBA 的新手。我在互联网上找到了一个很好地解决了我的问题的宏。但是还有一个小问题，这个宏只在活动表上运行。我想在我指定的工作表（例如 sheet1）上运行它。我把这个宏放在这里，请指导我解决这个问题。

Sub SelectByValue(Rng1 As Range, TargetValue As String)

Dim MyRange As Range
Dim Cell As Object

    'Check every cell in the range for matching criteria.

    For Each Cell In Rng1
        If Cell.Value > TargetValue Then
            If MyRange Is Nothing Then
                Set MyRange = Range(Cell.Address)
                Else
                Set MyRange = Union(MyRange, Range(Cell.Address))
            End If
        End If
    Next

    'Select the new range of only matching criteria

 MyRange.Select


End Sub

Sub CallSelectByValue()

    'Call the macro and pass all the required variables to it.
    Call SelectByValue(Range("A1:D500"), "")

End Sub

标签： excelvbaspreadsheet

解决方案

您可以尝试将工作表选择构建到Sub CallSelectByValue()可能？

我自己还不是专业人士，但这里有一个 coupla 建议：

1.在CallSelectByValueSub中定义工作表 （复制你的代码并编辑它：）

Sub CallSelectByValue()

Dim Sheet as WorkSheet
Set Sheet = Sheets("Sheet1") 'insert relevant sheet name

'Call the macro as before, but include the sheet reference in the range
Call SelectByValue(Sheet.Range("A1:D500"), "")

'Update after comments;
Sheet.Activate

End Sub

2.每次输入所需的表（在不同的表上重复使用子时）

Sub CallSelectByValue()

    'Find out what sheet to refer to
    Dim SheetName As String
    Dim Sheet As Worksheet
    FindSheetName:
    SheetName = InputBox("Please enter the name of the sheet you'd like it to refer to", "Sheet Selection")

'Check the typed value is actually a sheet name
    If IsEmpty(Sheets(SheetName)) Then
        MsgBox "That sheet couldn't be found. Try again", vbOkOnly+vbCritical, "Sheet not found"
        GoTo FindSheetName 'loops you back to re-try typing it in
    Else
'If it matches up, assign that value to the Sheet variable
        Sheet = Sheets(SheetName)
    End If


'Update after comments;
Sheet.Activate

    'Call the macro and pass all the required variables to it, including the sheet reference
    Call SelectByValue(Sheet.Range("A1:D500"), "")

End Sub

试试这些，让我知道它们是否有效。

推荐阅读