Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste Values (xp)

    <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.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Paste Values (xp)

    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

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Values (xp)

    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?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Values (xp)

    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>

    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Values (xp)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste Values (xp)

    All sorted out now, Hans. I used Legare's code but removed the "Exit For" statement.
    Thanks all for your help.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Paste Values (xp)

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •