exclude hidden worksheets while merging.

  • Thread starter Thread starter dhermus
  • Start date Start date
D

dhermus

I want to exclude hidden worksheets in a workbook from a merge along
with an existing summary sheet. Can anyone help with most practical
statement?
This is the code for excluding the summary sheet.


'Start loop

For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If

If sht.Name <> Sheet1.Name Then

' copy the data
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Offset(-1).Resize(, colCount))
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value


End If
 
For Each sht In wrk.Worksheets

If sht.Name <> Sheet1.Name Then
if sht.Visible = xlSheetVisible THEN

' copy the data
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Offset(-1).Resize(, colCount))
mst.Cells(65536,
1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

End If


End If
Next

I'm not sure why you have this
If sht.Index = wrk.Worksheets.Count Then
as your loop will look at each sheet
 
Back
Top