Sub CopyData()
Dim sh as Worksheet, rng as Range
for each sh in ActiveWorkbook.Worksheets
if lcase(sh.name) < "summary" then
set rng = sh.Range("A1").CurrentRegion
rng.copy Destination:=Worksheets("Summary") _
.Cells(rows.count,1).End(xlup)(2)
end if
Next
End Sub

This assumes your data starts in Cell A1 and there are no completely blank
rows or columns within the data.

if you have a header on each sheet, it will be copied. If you want to avoid
that
Sub CopyData()
Dim sh as Worksheet, rng as Range
for each sh in ActiveWorkbook.Worksheets
if lcase(sh.name) < "summary" then
set rng = sh.Range("A1").CurrentRegion.Offset(1,0)
rng.copy Destination:=Worksheets("Summary") _
.Cells(rows.count,1).End(xlup)(2)
end if
Next
End Sub

--
Regards.
Tom Ogilvy


"Dave" wrote in message
...
We have some infection control data which is stored on 12
sheets, 1 for each month. Each row on a sheet is data. We
would like to make a new sheet for totals, and have it
automatically copy all rows of data from the other 12 onto
itself, so basically we have an anual summary sheet.

How can this be accomplished?