如何在组合工作簿中的隐藏或非常隐藏的工作表时跳过它们

Question

我尝试跳过所有的"xlSheetHidden"或";xlSheetVeryHidden"。我最近开始使用VBA来帮助加快我的工作过程时，python不允许什么是需要的。我目前有以下代码:

Sub Merge_Sheets()
Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)
For i = 0 To Sheets.Count - 1
Work_Sheets(i) = Sheets(i + 1).Name
Next i
Sheets.Add.Name = "Combined Sheet"
Dim Column_Index As Integer
Column_Index = Worksheets(1).UsedRange.Cells(1, 1).Column
Dim Row_Index As Integer
Row_Index = 0
For i = 0 To Sheets.Count - 2
Set Rng = Worksheets(Work_Sheets(i)).UsedRange
Rng.Copy
Worksheets("Combined Sheet").Cells(Row_Index + 1, Column_Index).PasteSpecial Paste:=xlPasteValues
Row_Index = Row_Index + Rng.Rows.Count + 1            
Next i
Application.CutCopyMode = False
End Sub

我已尝试插入If .Visible = xlSheetVisible Then，但无法使其工作。

我也试过这样做:

For Each Sheets In ActiveWorkbook.Worksheets
If Sheet.Visible = xlSheetVisible Then

然而，这似乎仍然不起作用，任何帮助将是非常感激的。

Answer 1

您没有正确使用for each。在代码中，循环使用名称为Sheets的表，然后在循环中引用Sheet

For Each Sheets In ActiveWorkbook.Worksheets
If Sheet.Visible = xlSheetVisible Then '// Doesn't work!

所以你可能只需要修复这个变量命名:

For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then

或

For Each sht In ActiveWorkbook.Worksheets
If sht.Visible = xlSheetVisible Then

sht和ws是表/工作表对象的传统vba编码变量名。但是你可以用任何你喜欢的名字。但是，不能将Sheets作为变量名，因为这已经是内置Sheets集合的名称。

Answer 2

合并(追加)可见工作表

Option Explicit
Sub MergeWorksheets()

' Define constants.
Const dName As String = "Combined Sheet"
Const dFirstCellAddress As String = "A1"

' Reference the workbook ('wb').
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

Application.ScreenUpdating = False

' Delete the destination worksheet ('dws') if it exists.
Dim dws As Worksheet
On Error Resume Next
Set dws = wb.Worksheets(dName)
On Error GoTo 0
If Not dws Is Nothing Then
Application.DisplayAlerts = False
dws.Delete
Application.DisplayAlerts = True
End If

' Write the number of worksheets to a variable ('swsCount').
Dim swsCount As Long: swsCount = wb.Worksheets.Count

' Add the names of all the visible worksheets
' to an array ('WorksheetNames').

' A better choice here is to use a collection or a dictionary
' where it is not important to know the number of elements (items).
' But no harm done.
Dim WorksheetNames() As String: ReDim WorksheetNames(1 To swsCount)

Dim sws As Worksheet ' Current Source Worksheet
Dim n As Long ' Visible Worksheets Count(er)

For Each sws In wb.Worksheets
If sws.Visible = xlSheetVisible Then
n = n + 1
WorksheetNames(n) = sws.Name
End If
Next sws

If n = 0 Then
MsgBox "No visible worksheets found.", vbExclamation
Exit Sub
End If

' Resize the array to the actual number of found visible worksheets
' (not necessary since later we're looping with 'For n = 1 to n').
If n < swsCount Then ReDim Preserve WorksheetNames(1 To n)

' Add and reference a new worksheet, the destination worksheet ('dws').
' First sheet...
Set dws = wb.Worksheets.Add(Before:=wb.Sheets(1))
' ... or e.g. last sheet
'Set dws = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
dws.Name = dName ' rename
' Reference the first cell of the destination range ('dfCell').
Dim dfCell As Range: Set dfCell = dws.Range(dFirstCellAddress)

Dim srg As Range ' Current Source Range
Dim drg As Range ' Current Destination Range

For n = 1 To n
' Reference the source worksheet.
Set sws = wb.Worksheets(WorksheetNames(n))
' Reference the used range in the source worksheet.
Set srg = sws.UsedRange
' Reference the destination range, the destination cell
' resized by the number of rows and columns of the source range.
Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
' Write the values from the source range to the destination range.
drg.Value = srg.Value
' Reference the next destination first cell.
Set dfCell = dfCell.Offset(srg.Rows.Count)
Next n

Application.ScreenUpdating = True

' Inform to not wonder if the code has run or not.
MsgBox "Worksheets merged.", vbInformation
End Sub