How to skip hidden or veryhidden sheets in a workbook when combining them-CodePudding

I am trying to skip all of the sheets that are"xlSheetHidden" or "xlSheetVeryHidden". I have recently started using VBA to help speed up processes at my work when python wasn't allowing for what was needed. I currently have the following code:

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

I have tried inserting If .Visible = xlSheetVisible Then but cannot get it to work.

I have also tried to make it work with:

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

However this still doesn't seem to work, any help would be greatly appreciated.

CodePudding user response：

Merge (Append) Visible Worksheets

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 = True
    
    ' 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

CodePudding user response：

You did not use the for each correctly. In your code you loop over sheets with the name Sheets, then in the loop you refer to Sheet

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

So you probaby only needed to fix up this variable naming:

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

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

sht and ws are traditional vba coding variables for sheets. But you can use any name you like. However, not Sheets as a variable name, as that is already the name of the built-in Sheets collection.