After serious thinking I realize I'm missing a closing parenthesis
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