Hi All,

In a previous Thread: "Copy and Paste LAST ROW of data", Tom Ogilvy and Mike
Fogleman both provided me with Great VB code.

Tom's code copies the last row of (contiguous) data to the next blank row
below, across multiple worksheets.

Tom Ogilvy
Sub CopyLast()
Dim r1 as Range, r2 as Range
Dim sh as worksheet
for each sh in worksheets(Array("sheet1", "sheet3", "sheet5"))
set r1 = sh.Columns(1).specialCells(xlConstants,xlNumbers). Areas(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

If possible, I would like to adjust the code, still copying the last row of
data BUT the data will be non-contiguous (one or more blank cells) in the row
and it will contain constants as well as formulae.

-------------------------------------------------------------------

Mike Fogleman also provided this treasu
Is there other data surrounding this contiguous data that we need to avoid?
If not, then this simple code will do it:

Sub test()
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Rows(LRow).Copy Rows(LRow + 1)
End Sub

I added the following lines to Mike's code but could not get it to goto each
worksheet in turn.
Dim sh as worksheet
for each sh in worksheets(Array("sheet1", "sheet3", "sheet5"))
Next sh

Help very much appreciated.

Thanks
Sam

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