exclude hidden worksheets while merging.

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
 
P

Patrick Molloy

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top