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).EntireRow. _
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" <(E-Mail Removed)> wrote in message
news:03327C7A-F856-4B40-8710-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> >>> news:eQH5#(E-Mail Removed)...
>> >>>> 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" <(E-Mail Removed)> wrote in message
>> >>>> news:9953AD70-156B-4F81-8721-(E-Mail Removed)...
>> >>>>> 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
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>
Similar Threads
