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.