Add one more line after the For Each statement:

for each sh in worksheets(Array("sheet1", "sheet3", "sheet5"))
sh.Activate

Since the main code does not qualify which sheet the ranges, rows, columns,
etc are on, you need to Activate each sheet in its' turn so the code will
act upon the proper sheet.

Mike F
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:77f689e239334@uwe...
Hi Tom,

Thank you very much for further input.

When I run Sub CopyLast(), it is adding two new rows of data to the first
worksheet and no new row to the second worksheet specified.

I have replied to your questions below.

Cheers
Sam

Tom Ogilvy wrote:
After investing more time, I guess he means if there are multiple areas in
column A.

Assuming the values are constants and not formulas, then this modification
should handle that.

Sub CopyLast()
Dim r1 as Range, r2 as Range
Dim sh as worksheet
for each sh in worksheets(Array("sheet1", "sheet3", "sheet5"))
set r1 = Columns(1).specialCells(xlConstants,xlNumbers).Are as(1)
set r1 = r1(r1.count)
if isempty(r1(1,2)) then
set r2 = r1
else
set r2 = r1.end(xltoRight)
end if
Range(r1,r2).Copy r1(2)
Next sh
End Sub

this assumes going down column A
Yes

1 or more blank cells
Yes, but before the start of contiguous range of cells and possibly after.

a contiguous range of cells with number constants
Yes

at least one blank cell (then there may be:
sometimes

other data in cells including blanks. )
sometimes

If it is different from that, say how different.
If there is at least one blank cell below the contiguous range of cells, I
would like that data excluded from the copy process; the last row should
be
qualified by the end of the contiguous data, anything after the contiguous
data should be excluded.

I am not sure what dragon Mike is trying to slay - I didn't see it in
your
initial description. I guess cells(rows.count,1).offset(1,1) being not
[quoted text clipped - 78 lines]

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200709/1