I just sent the answer back to you. The problem appears to be in the name of
the carry overs sheet... since you said the name was "Carryovers" (one word)
in your original message, I used that; however, I see on the workbook you
sent me that the sheet is actually named "Carry overs" (two words)... make
that change in my posted code and the code should work fine for you.

--
Rick (MVP - Excel)



"ajm1949" wrote in message
...
Thanks Rick
Email is on the way
Alan

"Rick Rothstein" wrote:

I'm using XL2003 as well and both of those macros worked on the sample
data
I made up. Is there any chance you can send me a copy of your workbook so
I
can see what is going on directly (it would be okay for you to replace
real
data with fake data, but I'm interested in the values in Column N on both
sheets... in particular, whether they are typed in values or formulas? If
so, set it up so that its in the condition just prior to needing the
macros
run. You will need to remove the NO.SPAM stuff from my email address.

--
Rick (MVP - Excel)



"ajm1949" wrote in message
...
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


.

.