Okay, let me see if I have this straight. There is data on the Sales sheet
in Columns A thru T and also in some columns after Column T as well. You
want to move only those rows between Columns A thru T for those rows where
Column N is empty over to the Carryovers sheet. When you are completely
done, only those rows from your original data (all columns) on the Sales
sheet where Column N is empty will remain on the Sales sheet (that is, at
the end, all cells in Column N on the Sales sheet will be empty). If this is
correct, give the following macro a try...

Sub ProcessCarryovers()
On Error Resume Next
With Sheets("Sales")
.Columns("N").SpecialCells(xlCellTypeBlanks).Entir eRow. _
Copy Sheets("Carryovers").Range("A2")
.Range("A2", .Cells(.Rows.Count, .Columns.Count)).Clear
End With
With Sheets("Carryovers")
.Columns("N").SpecialCells(xlCellTypeBlanks). _
EntireRow.Copy Sheets("Sales").Range("A2")
.Range("U2", .Cells(.Rows.Count, .Columns.Count)).Clear
End With
End Sub

Since I'm not 100% sure of this, make sure you test the macro on a copy of
your workbook (or, if you use your original workbook, make sure not to save
it) until you are sure it does what you want. Just so you know, what this
macro does is copy the **entire** row for the blank cells in Column N over
to your Carryovers sheet, then it clears everything except the header row
from the Sales sheet (you do have a header row on it, right?), then it
copies the entire rows from the Carryovers sheet back to the Sales sheet,
and then finally it clears Columns U thru to the last Column on the
Carryovers sheet so that only the date in Columns A thru T remains.

--
Rick (MVP - Excel)



"ajm1949" wrote in message
...
Hi Garry
I thought of it more a frienldy rivalry but it's great to have someone to
look up to like Rick. if you are a newbie then i am still wearing
nappies...LOL

Perhaps i should explain in more detail.

In short we need to clear the delivered cars from last months carryover
sheet leaving undelivered cars before adding this months to the carryover
sheet.

Your code does this exactly how we need it. My first post was not quite
correct in that I said moving to A2 was OK forgetting that some data may
need
to remain. Maybe Rick's code is faster but can it be modified to add data
after the last used row?

ClearContents (Columns A:T)would be better as sometimes there maybe other
information after column T. This would be a separate operation before
moving
carryovers

This is just a part of the end of month sales log update. I already have
most of the other code in place.
Steps are
1. Archive Sales sheet (create a copy and name it)
2 . Remove delivered cars from Carryovers Sheet
3. Move carryovers from sales
4. Clear sales sheet

Many thanks to everyone who gives their time to help.

Cheers
Alan
ajm1949
"GS" wrote:

Hi Alan,
Rick and I aren't competing; he's the veteran, I'm the newbie. That
said, I like his coding and aspire to be as good as he is some day, and
so the competition is with myself and my VB[A} skills.

<<How do I clear rows with an entry in column N?
What do you mean by 'clear rows'? Do you mean ClearContents or Delete?
Do you want to do it during the MoveCarryOvers routine OR after that
routine is done. (During will be a bit tricky if deleting rows)

Basically it would involve repeating the process with ClearContents OR
EntireRow.Delete

Garry
--


It happens that ajm1949 formulated :
Hello Rick and Garry
Seems like you guys are a bit competitive...Keep up the great work. I
am
also learning a lot from all the posts here. The first code sample had
a
minor error.
Sub MoveCarryOvers()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr1 As Long, rng As Range
Dim c As Object
Set sh1 = Sheets("Sales")
Set sh2 = Sheets("Carry overs")
lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("N1:N" & lr1)
For Each c In rng
If c.Value = "" Then
Range("A" & c.Row).Resize(1, 21).Copy _
sh2.Range("A" & sh2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row)
End If
Next
End Sub

Rick's sample definitely runs noticably faster. Thank Rick.

My next question is this.

How do I clear rows with an entry in column N?

Cheers
Alan

"GS" wrote:

Rick Rothstein was thinking very hard :
Here is a considerably shorter (and I believe much faster) macro
which does
what your macro does...

Sub terranean()
On Error Resume Next
Intersect(Sheets("Sales").Columns("A:T"), Sheets("Sales"). _
Columns("N").SpecialCells(xlCellTypeBlanks). _
EntireRow).Copy Sheets("Carryovers").Range("A2")
End Sub

--
Rick (MVP - Excel)



"JLGWhiz" wrote in message
...
This might work

Sub terrain()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim lr1 As Long, lr2 As Long, rng As Range
Set sh1 = Sheets("Sales")
Set sh2 = Sheets("Carryovers")
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("N1:N" & lr)
For Each c In rng
If c.Value = "" Then
Range("A" & c.Row).Resize(1, 21).Copy _
sh2.Range("A" & sh2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row)
End If
Next
End Sub




"ajm1949" wrote in message
...
Hi all
I have looked through the forums but can't find what i need.

In each row that column N (Sheet name is Sales) is empty I want to
copy
columns A:T to another worksheet (Sheet Name is Carryovers)
starting at
A2 Hope this is clear enough

many thanks in advance

Alan
ajm1949



Now, Rick, I thought I was going to beat you to this one. Not
surprised
I didn't, ..just a bit disappointed in myself for not getting my reply
finalized sooner. (It's been a distracting day today)

Anyway, I just thought I'd let you know that I'm learning a lot from
your posts, and so my disappoinment is offset by the discovery that I
was about to duplicate what you posted. Credit goes to you for that!
Thanks for all the good learning content that you post!

regards,
Garry


.



.