I have a workbook that contains multiple identical worksheets and one
summary sheet. I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.


'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
'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
'Put data into the Master worksheet
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

Unless i misunderstood, try this:
'Start loop
For Each sht In wrk.Worksheets

if if not sht.name = "YourSummarySheetNameHere" then 'If not
summary sheet, do the stuff, otherwise ignore.


'If worksheet in loop is the last one, stop execution
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
'Put data into the Master worksheet
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize
(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

End if



On 17 July, 16:20, dhermus wrote:
I have a workbook that contains multiple identical worksheets and one
summary sheet. *I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.

* * '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
* * * * 'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
* * * * Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
* * * * 'Put data into the Master worksheet
* * * * mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

oops - type, you surely don't need the 2 'ifs' in this:
if if not sht.name


On 17 July, 16:39, AB wrote:
Unless i misunderstood, try this:
* * *'Start loop
* * *For Each sht In wrk.Worksheets

* * * if if not sht.name = "YourSummarySheetNameHere" then 'If not
summary sheet, do the stuff, otherwise ignore.

* * * * *'If worksheet in loop is the last one, stop execution
* * * * *If sht.Index = wrk.Worksheets.Count Then
* * * * * * *Exit For
* * * * *End If
* * * * *'Data range in worksheet - starts from second row as first
*rows are the header rows in all worksheets
* * * * *Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
*(xlUp).Resize(, colCount))
* * * * 'Put data into the Master worksheet
* * * * *mst.Cells(65536, 1).End(xlUp).Offset(1).Resize
(rng.Rows.Count,
*rng.Columns.Count).Value = rng.Value

* * *End if

On 17 July, 16:20, dhermus wrote:



I have a workbook that contains multiple identical worksheets and one
summary sheet. *I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.

* * '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
* * * * 'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
* * * * Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
* * * * 'Put data into the Master worksheet
* * * * mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows..Count,
rng.Columns.Count).Value = rng.Value- Hide quoted text -

- Show quoted text -

you don't need to exit the FOR the way that you have done, just check that
the worksheet isn't the new sheet

FOR EACH sht IN wrk.Worksheets

IF sht.Name < mst.Name THEN

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

END IF

NEXT



Note: where does the variable colCount get its value?
maybe
colCount = sht.Range("A1").End(xlRight).Column




"dhermus" wrote in message
...
I have a workbook that contains multiple identical worksheets and one
summary sheet. I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.


'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
'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
'Put data into the Master worksheet
mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value

On Jul 17, 11:45*am, "Patrick Molloy"
wrote:
you don't need to exit the FOR the way that you have done, just check that
the worksheet isn't the new sheet

FOR EACH sht IN wrk.Worksheets

* * IF sht.Name < mst.Name THEN

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

* * END IF

NEXT

Note: where does the variable colCount *get its value?
maybe
colCount = sht.Range("A1").End(xlRight).Column

"dhermus" wrote in message

...



I have a workbook that contains multiple identical worksheets and one
summary sheet. *I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.

* *'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
* * * *'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
* * * *Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
* * * *'Put data into the Master worksheet
* * * *mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value- Hide quoted text -

- Show quoted text -

Thank you, this provided what I needed.

On Jul 17, 11:45*am, "Patrick Molloy"
wrote:
you don't need to exit the FOR the way that you have done, just check that
the worksheet isn't the new sheet

FOR EACH sht IN wrk.Worksheets

* * IF sht.Name < mst.Name THEN

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

* * END IF

NEXT

Note: where does the variable colCount *get its value?
maybe
colCount = sht.Range("A1").End(xlRight).Column

"dhermus" wrote in message

...



I have a workbook that contains multiple identical worksheets and one
summary sheet. *I am trying to add a new worksheet in the workbook
with the merged data from the identical sheets, but I have not been
able to exclude the summary worksheet.

* *'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
* * * *'Data range in worksheet - starts from second row as first
rows are the header rows in all worksheets
* * * *Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End
(xlUp).Resize(, colCount))
* * * *'Put data into the Master worksheet
* * * *mst.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count,
rng.Columns.Count).Value = rng.Value- Hide quoted text -

- Show quoted text -

I found I have multiple hidden worksheets that I must also exclude
from my merge. Can you help with this statement?