Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude Hidden Row Values | Excel Worksheet Functions | |||
Exclude hidden sheets from formatting | Excel Programming | |||
Copy Worksheet and Exclude Hidden fields | Excel Worksheet Functions | |||
Exclude hidden workbooks from list | Excel Programming | |||
Exclude hidden data from summary totals | Excel Discussion (Misc queries) |