I was originally intending to write the loop as follows for better
clarit...

Dim wks As Worksheet, rngDataToCopy As Range, rngTarget As Range
Dim lLastRow&, lLastCol&
Const lStartRow& = 7 '1st row containing data on source sheets

For Each wks in ActiveWorkbook.Worksheets
If Not InStr(sSheetsNotToCopy, wks.Name) 0 Then
'Here's where you copy the data to wksTarget
'I suggest you use a consistent method to find the last row of
'each sheet to be copied, so as not to hard-code the # of rows.

'Get the range of data
lLastRow = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row
lLastCol = wks.Cells(5, wks.Columns.Count(.End(xlToLeft).Column
'The above assumes data starts same row on every source sheet.

'Copy the data to wksTarget on the next blank row
'(Assumes first row only is blank, or has/will have headings)
Set rngTarget = _
wksTarget.Range("A" & wksTarget.UsedRange.Rows.Count + 1)
Set rngDataToCopy = _
wks.Range("A" & lStartRow, wks.Cells(lLastRow, lLastCol)
rngDataToCopy.Copy Destination:=rngTarget
End If
Next 'wks

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion