Consulting

Results 1 to 2 of 2

Thread: How to combine hidden sheets using VBA

  1. #1
    VBAX Newbie
    Joined
    Mar 2020
    Posts
    1
    Location

    How to combine hidden sheets using VBA

    I have a worksheet and I'm trying merge multiple hidden sheets together I only want the hidden sheets the visible sheets I don't want to merge them. Currently I'm using this code:
    Sub Combine()
    'UpdatebyExtendoffice20180205
        Dim I As Long
        Dim xRg As Range
    
        On Error Resume Next
        Worksheets.Add Sheets(1)
        ActiveSheet.Name = "Combined"
    
       For I = 2 To Sheets.Count
                Set xRg = Sheets(1).UsedRange
                If I > 2 Then
                       Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
                End If
    
                Sheets(I).Activate
                ActiveSheet.UsedRange.Copy xRg
        Next
    End Sub

    I'm new to using VBA therefore I would like to ask if someone can help combine only the hidden sheet
    Last edited by SamT; 03-27-2020 at 12:21 PM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    For each Sht in Sheets
      If Sht.Visible = 0 then
        Set xRg = Sheets("Combined").UsedRange
        Set xRg = Sheets("Combined").Cells(xRg.Rows.Count + 1, 1)
        Sht.UsedRange.Copy xRg
      End If
    Next
    xlSheetHidden = 0
    xlSheetVeryHidden = 2
    xlSheetVisible = -1
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •