As long as no other column will have more rows filled in than Column A, that
should work fine. The code I gave you, which should work, finds the largest
row number no matter what column that row is in and uses that to set the
copy position.

--
Rick (MVP - Excel)



"ajm1949" wrote in message
...
Hi Rick

Have been doing some more research and came up with this modofication of
you
first code sample
Sub terranean1()

Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _

Columns("N").SpecialCells(xlCellTypeBlanks). _
EntireRow).Copy Sheets("Carry
overs").Cells(Rows.Count, 1).End(xlUp)(2)

End Sub

This seems to work and runs quickly. Can you see any problems with this?

Still doing some more homework on the code to clean up the Carry Overs
sheet

Thanks again for your help

Cheers

Alan

"ajm1949" wrote:

Hi Rick
Sorry to say that the new code samples seem to be missing something as
they
don't work.

Revised Sub terranean come up with error 9 subscript out of range with
the
lastrow=etc highlighted as the culprit.

Sub ject macro does nothing at all

I am using excel 2003 with all the latest service packs an updates

Cheers
Alan

"Rick Rothstein" wrote:

Damn, I forgot the On Error statement (needed in case all of Column N
is
blank)...

Sub ject()
On Error Resume Next
Sheets("Carryovers").Range("N2:N" & Rows.Count). _
SpecialCells(xlCellTypeConstants).EntireRow.Delete
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Also need a macro to clear data from the carryover sheet where
column N has a value. This macro will be run before the one to
move the data from sales.

When you say "clear", I am assuming you mean to delete the entire row
so
that no gaps appear in your data rows. You can use this macro to
delete
the rows of data on the Carryovers sheet where Column N has a value
placed
in it...

Sub ject()
Sheets("Carryovers").Range("N2:N" & Rows.Count). _
SpecialCells(xlCellTypeConstants).EntireRow.Delete
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in
message
...
Here is a revision for Sub Terranean for you to try...

Sub Terranean()
Dim LastRow As Long
LastRow = Sheets("Carryovers").Cells.Find(What:="*", _
SearchOrder:=xlRows,
SearchDirection:=xlPrevious, _
LookIn:=xlValues).Row
On Error Resume Next
Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _
Columns("N").SpecialCells(xlCellTypeBlanks). _
EntireRow).Copy Sheets("Carryovers").Cells(LastRow + 1,
"A")
End Sub

--
Rick (MVP - Excel)


"ajm1949" wrote in message
...
Hi Rick
Sorry I wasn't clear enough.

The Sub Terranean macro is the quickest but we need to put the data
after
some existings data. That's the one that I hope can be modified.

Also need a macro to clear data from the carryover sheet where
column N
has
a value. This macro will be run before the one to move the data
from
sales.


Cheers

Alan


.