exclude hidden worksheets while merging.
 

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

exclude hidden worksheets while merging.
 

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





"dhermus" wrote in message
...
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