☰

VBA 代码隐藏多张工作表上的行

tag-icon • tag-icon microsoft-excel worksheet-function vba
VBA 代码隐藏多张工作表上的行

对此非常陌生。有没有办法让选项单元格 K473 位于选项卡 1 上，并将选项卡 2 上的隐藏行更改为 11？下面的操作在单个工作表上完美运行，但希望根据第一张工作表上的选择在多张工作表上获得相同的结果。

提前致谢，约翰

Private Sub Worksheet_Change（ByVal 目标作为范围）

Dim iCell As Range: Set iCell = Intersect(Range("k473"), Target)
If iCell Is Nothing Then Exit Sub

If iCell.Value = "Quoted_Rates" Then
    Rows("481:492").Hidden = False
    Rows("474:480").Hidden = True
ElseIf iCell.Value = "Cost_Rate" Then
    Rows("481:492").Hidden = True
    Rows("474:480").Hidden = False
'Else ' do nothing
End If

子目录结束

答案1

尝试这样的操作：

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCell As Range: Set iCell = Intersect(Range("k473"), Target)
If iCell Is Nothing Then Exit Sub

Sheets(Array("tab 1", "tab 2", "tab 3", "tab 4", "tab 5", _
    "tab 6", "tab 7", "tab 8", "tab 9", "tab 10", "tab 11")).Select
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
     ws.Rows("481:492").Hidden = iCell.Value = "Cost_Rate"
     ws.Rows("474:480").Hidden = iCell.Value = "Quoted_Rates"
Next ws
Me.Select
End Sub

相关内容