THE HIDDEN SHEET FACTOR Dear Hans V MVP, & other respected sirs, Firstly I would

Question

THE HIDDEN SHEET FACTOR

Dear Hans V MVP, & other respected sirs,

Firstly I would like to thank MR. HANS V. MVP for providing his valuable reply with response to my query. I am looking for his help in future w.r.t other queries.

Suppose, A workbook has some hidden sheets and I want to work on the sheets that are NOT HIDDEN. Following is a code that copies all the data from all the sheets to a summary sheet. Now, if some worksheets are hidden, and I do not know the names of the sheets, and I want to copy the data of the UNHIDDEN Sheets only, what modifications to be made in the following code ?

Regards,

Thank !!

Sub CopyRangeFromMultiWorksheets()     Dim sh As Worksheet     Dim DestSh As Worksheet     Dim Last As Long     Dim CopyRng As Range     With Application         .ScreenUpdating = False         .EnableEvents = False     End With     'Delete the sheet "RDBMergeSheet" if it exist     Application.DisplayAlerts = False     On Error Resume Next     ActiveWorkbook.Worksheets("RDBMergeSheet").Delete     On Error GoTo 0     Application.DisplayAlerts = True     'Add a worksheet with the name "RDBMergeSheet"     Set DestSh = ActiveWorkbook.Worksheets.Add     DestSh.Name = "RDBMergeSheet"     'loop through all worksheets and copy the data to the DestSh     For Each sh In ActiveWorkbook.Worksheets         'Loop through all worksheets except the RDBMerge worksheet and the         'Information worksheet, you can ad more sheets to the array if you want.         If IsError(Application.Match(sh.Name, _                                      Array(DestSh.Name, "Information"), 0)) Then             'Find the last row with data on the DestSh             Last = LastRow(DestSh)             'Fill in the range that you want to copy             Set CopyRng = sh.Range("A2:G2")             'Test if there enough rows in the DestSh to copy all the data             If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then                 MsgBox "There are not enough rows in the Destsh"                 GoTo ExitTheSub             End If             'This example copies values/formats, if you only want to copy the             'values or want to copy everything look at the example below this macro             CopyRng.Copy             With DestSh.Cells(Last + 1, "A")                 .PasteSpecial xlPasteValues                 .PasteSpecial xlPasteFormats             End With             'Optional: This will copy the sheet name in the H column             DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name         End If     Next sh      ExitTheSub:     Application.GoTo DestSh.Cells(1)     'AutoFit the column width in the DestSh sheet     DestSh.Columns.AutoFit.     With Application.         .ScreenUpdating = True         .EnableEvents = True     End With End Sub Function LastRow(sh As Worksheet)     On Error Resume Next     LastRow = sh.Cells.Find(what:="*", __            After:=sh.Range("A1"), ,            Lookat:=xlPart, _            LookIn:=xlFormulas, _            SearchOrder:=xlByRows, _          SearchDirection:=xlPrevious, _            MatchCase:=False).Row     On Error GoTo 0 End Function.

Explanation / Answer

Hi,

This code will now ignore all hidden sheets. Changes in bold.

Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range
  
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
  
    'Delete the sheet "RDBMergeSheet" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
  
    'Add a worksheet with the name "RDBMergeSheet"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "RDBMergeSheet"
  
    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Visible Then
    MsgBox sh.Name

        'Loop through all worksheets except the RDBMerge worksheet and the
        'Information worksheet, you can ad more sheets to the array if you want.
        If IsError(Application.Match(sh.Name, _
                                     Array(DestSh.Name, "Information"), 0)) Then
  
            'Find the last row with data on the DestSh
            Last = LastRow(DestSh)
  
            'Fill in the range that you want to copy
            Set CopyRng = sh.Range("A2:G2")
  
            'Test if there enough rows in the DestSh to copy all the data
            If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                MsgBox "There are not enough rows in the Destsh"
                GoTo ExitTheSub
            End If
  
            'This example copies values/formats, if you only want to copy the
            'values or want to copy everything look at the example below this macro
            CopyRng.Copy
            With DestSh.Cells(Last + 1, "A")
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
            End With
  
            'Optional: This will copy the sheet name in the H column
            DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name
  
        End If
    End If
    Next sh
     
ExitTheSub:
  
    Application.GoTo DestSh.Cells(1)
  
    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit
  
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(what:="*", _
           After:=sh.Range("A1"), _
           Lookat:=xlPart, __
           LookIn:=xlFormulas, __
           SearchOrder:=xlByRows, __
         SearchDirection:=xlPrevious,._ _
           MatchCase:=False).Row.
    On Error GoTo 0
End Function

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.