Paste Values (xp)

[bandido]

<P ID="edit" class=small>(Edited by bandido on 06-Mar-05 16:18. To add an extra detail.)</P>I am trying to extract rows of data from a worksheet that meet certain criteria. I have done this successfully with the following code:

Sub AlertMe()
Sheets("Alert").Select
Sheets("Alert").Range("a3:ai500").ClearContents
x = Sheets("Scores").Range("A1").CurrentRegion.Rows.Co unt
For n = 1 To x
If Sheets("Scores").Range("AI" & n + 3).Value < 36 Then _
Sheets("Scores").Range("A" & n + 3).EntireRow.Copy _
Destination:=Sheets("Alert").Range("A65536").End(x lUp).Offset(1)
Next n
Sheets("Alert").Range("AL1:BO500").ClearContents
SortAlert
End Sub

"SortAlert" simply sorts the data in ascending order. My problem is that I wish to paste values and formats but not formulae to the destination but I'm experiencing difficulty in modifying the code. I'm sure that it's quite simple but I'm stumped for the moment.
Any help would be appreciated.

[sdckapr]

You can use PAstespecial values instead of paste:

Instead of
Sheets("Scores").Range("A" & n + 3).EntireRow.Copy _
Destination:=Sheets("Alert").Range("A65536").End(x lUp).Offset(1)

Use:
Sheets("Scores").Range("A" & n + 3).EntireRow.Copy
Sheets("Alert").Range("A65536").End(xlUp).Offset(1 , 0).PasteSpecial (xlValues)
Sheets("Alert").Range("A65536").End(xlUp).PasteSpe cial (xlPasteFormats)

YOu should probably also change:
Sheets("Alert").Range("a3:ai500").ClearContents

to:
Sheets("Alert").Range("a3:ai500").Clear

to ensure that the formatting is cleared before you add new items for the rows you don't put new data into

Steve

[bandido]

Thanks for the reply and apologies for the delay in replying to your suggestions.
I did as you suggested but I am getting multiple rows being pasted. Any ideas?

[LegareColeman]

I don't understand. Your paste code is in a loop. It will copy and all rows that the loop checks where the value in column AI is less than 36. Are you trying to say that you want to stop the loop after the first row is copied? If that is what you are asking, then change your If statement to something like this:

<pre> If Sheets("Scores").Range("AI" & n + 3).Value < 36 Then
Sheets("Scores").Range("A" & n + 3).EntireRow.Copy
Sheets("Alert").Range("A65536").End(xlUp).Offset(1 , 0).PasteSpecial (xlValues)
Sheets("Alert").Range("A65536").End(xlUp).PasteSpe cial (xlPasteFormats)
Exit For
End If
</pre>

[bandido]

Apologies for not making myself clear, Legare. The problem is that multiple rows of the same data are being pasted. With my original code, the correct data was pasted, but on using Steve's suggested code, the problem I have outlined happens. Eg

Instead of:

J Smith
J Bloggs
A Jones etc, I get:

J Smith
J Smith
J Smith
J Smith
J Bloggs
J Bloggs
A Jones

Some rows are pasted 5 times, others mostly twice and some only once. There appears to be no pattern that I can see.
I hope that clarifies the situation.
Thanks

[bandido]

All sorted out now, Hans. I used Legare's code but removed the "Exit For" statement.
Thanks all for your help.

[HansV]

Your original code had a continuation character _ after If ... Then. This means that the first line below will be executed only if the condition is true, but the lines below that will be executed unconditionally - they are not subject to the If ... Then. In the modified code proposed by Steve, you must not use a continuation character, but an If ... End If block, as indicated by Legare. This works OK on a quick sample I created.

If that doesn't help, could you post a stripped down copy of your workbook, with data modified to protect the guilty?