PDA

View Full Version : How to combine hidden sheets using VBA



help
03-27-2020, 06:26 AM
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

SamT
03-27-2020, 12:31 PM
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