VBA：Excel 循环 - 隐藏/取消隐藏工作表

VBA: Excel loop - hiding/unhiding sheets

提问by user2742754

Sub Hideall_butlast_10()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

i = Worksheets.Count
For x = 10 To i
    Sheets(x).Select
    ActiveSheet.Visible = xlSheetHidden
Next x
End Sub

Hey guys

大家好

I am trying to edit my code above to unhide all the sheets then hide all the sheets EXCEPT the first 5 and the last 10 (The 10 will change) - But the first 5 are always showing

我正在尝试编辑上面的代码以取消隐藏所有工作表，然后隐藏所有工作表，除了前 5 个和最后 10 个（10 个会改变） - 但前 5 个总是显示

My workbook has between 50-300 pages (changes daily)

我的工作簿有 50-300 页（每天更改）

Hope someone can point me in the right direction

希望有人能指出我正确的方向

Thanks

谢谢

回答by dee

Sub Hideall_butlast_10()
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

    Const lowerBound As Integer = 5
    Const upperBound As Integer = 10

    Dim i, x
    i = Worksheets.Count
    If (i > lowerBound + upperBound) Then
        For x = lowerBound + 1 To (i - upperBound)
            Sheets(x).Visible = xlSheetHidden
        Next x
    End If
End Sub 

回答by Daniel

Here's some over the top code I just wrote for ya...

这是我刚刚为你写的一些最重要的代码......

Public Enum ToggleSheets
    Show = 1
    Hide = 2
    Toggle = 3
End Enum
Sub ToggleSheets(Optional ByVal startIndex As Integer = 1, Optional ByVal StopIndex As Integer = 0, Optional ByVal Toggle As Integer = Toggle)
    If StopIndex = 0 Then StopIndex = ThisWorkbook.Sheets.Count
    For i = startIndex To StopIndex
        Select Case Toggle
        Case Show
            ThisWorkbook.Sheets(i).Visible = xlSheetVisible
        Case Hide
            ThisWorkbook.Sheets(i).Visible = xlSheetHidden
        Case Toggle
            If ThisWorkbook.Sheets(i).Visible = 0 Then
                ToggleSheets i, i, Show
            Else
                ToggleSheets i, i, Hide
            End If
        End Select
    Next
End Sub

Sample Calls:

示例调用：

ToggleSheets , , hide'hides all sheets

ToggleSheets 1,5, show 'displays first 5 sheets.
togglesheets workbooks.count - 9, , show ' shows last 10 sheets

回答by Ejaz Ahmed

I noticed that you are only selecting the sheet, and not activating it. You need to "Activate" the sheet before using the ActiveSheet object.

我注意到您只是选择了工作表，而不是激活它。在使用 ActiveSheet 对象之前，您需要“激活”工作表。

Here is a modified version of your code that works:

这是您的代码的修改版本：

Sub Hideall_butlast_10()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

Dim i, x As Long

i = Worksheets.Count
For x = 1 To i - 10
    Sheets(x).Activate
    ActiveSheet.Visible = xlSheetHidden
Next x
End Sub

Here is how I would implement it:

这是我将如何实现它：

Option Explicit

Sub HideAllSheets()

Dim wkbBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet

Set wkbBook = Excel.ActiveWorkbook

On Error Resume Next ' to suppress the error that turns up when trying to hide the last sheet
For Each wksSheet In wkbBook.Worksheets
    wksSheet.Visible = xlSheetHidden
Next wksSheet
Err.Clear
On Error GoTo 0

End Sub

Sub UnhideAllSheets()

Dim wkbBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet

Set wkbBook = Excel.ActiveWorkbook

On Error Resume Next
For Each wksSheet In wkbBook.Worksheets
    wksSheet.Visible = xlSheetVisible
Next wksSheet
Err.Clear
On Error GoTo 0

End Sub

Sub UnhideFirstXSheets(ByVal lngX As Long)

Dim wkbBook As Excel.Workbook
Set wkbBook = Excel.ActiveWorkbook
Dim wksSheet As Excel.Worksheet

Dim lngCount As Long

For lngCount = 1 To lngX
    Set wksSheet = wkbBook.Sheets(lngCount)
    wksSheet.Visible = xlSheetVisible
Next lngCount

End Sub

Sub UnhideLastXSheets(ByVal lngX As Long)

Dim wkbBook As Excel.Workbook
Set wkbBook = Excel.ActiveWorkbook
Dim wksSheet As Excel.Worksheet

Dim lngCount As Long
Dim lngSheetCount As Long
lngSheetCount = wkbBook.Worksheets.Count

For lngCount = 1 To lngX
    Set wksSheet = wkbBook.Sheets(lngSheetCount - lngCount + 1)
    wksSheet.Visible = xlSheetVisible
Next lngCount

End Sub

Sub DoWhatYouWant()
Application.ScreenUpdating = False
Application.EnableEvents = False
Call UnhideAllSheets 'Unhide all the sheets (not really necessary
Call HideAllSheets 'Hide everything. Leaves the last sheet visible, because all the sheets in a workbook cannot be hidden
Call UnhideFirstXSheets(5) 'Unhides the first X sheets
Call UnhideLastXSheets(10) 'Unhides the last X sheets
Application.EnableEvents = True
Application.ScreenUpdating = False
End Sub

Run the 'DoWhatYouWant' macro and that should do. Feel free the change the 5 and 10 as per your requirement.

运行“DoWhatYouWant”宏就可以了。根据您的要求随意更改 5 和 10。