Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Don't know what to call it (Excel 97, SR2)

    Hi. I have a list of items. I want to be able to select some of the items and copy them to another sheet. I want to place a "Yes" in a cell in the row of each item I need to copy. When the row has a "Yes", I want it to copy each item with a "Yes" and place them in another sheet. I would like to do this with a macro/VBA. Can someone show me how to do this? I think it is probably simple. If more information is needed or the above doesn't make any sense, let me know. Thanks.

  2. #2
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    Lonnie,
    This is one way, if I've understood you correctly:

    The items are in cells A1:A20 and the 'yes's are in cells B1:B20; Sheet2 contains a title for the new list in cell A1

    Sub CopyItems()

    Dim cl As Range

    For Each cl In Range("A1:A20")
    If cl.Offset(,1).Value = "yes" Then
    cl.Copy Destination:=Sheet2.Range("A65536").End(xlUp).Offs et(1)
    End If
    Next

    End Sub

    As ever, I'm sure there are more elegant ways!

    HTH

    graeme

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    You could also use the autofilter function on your list and select only those records containing 'yes'.
    Then select the items shown, copy them and paste them into the other sheet.
    A bit messy but no VB required.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    This is also not too neat, but it's only one line of VBA....

    Create another range, with matching column headings as your list, with the word 'yes' below the column header which will contain 'yes' in your list. Call that range 'criteria'.

    If your original list range is called 'myrange' and your target range for the copied records is 'targetrange' then the following line if VBA will do your work:-

    <pre>Range("myrange").AdvancedFilter xlFilterCopy, Range("criteria"), Range("targetrange")</pre>


  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    Hi, graeme. Thanks for your help. It has gotten me a long way, but I need a little more help.

    This is your code that I revised slightly:

    ************************************************** **
    Sub CopyItems()

    Dim CopyYes As Range

    For Each CopyYes In Range("I38:I200")
    If CopyYes.Value = "y" Then
    CopyYes.EntireRow.Copy Destination:=Sheet2.Range("A65536:J65536").End(xlU p).Offset(1)

    End If
    Next

    End Sub
    ************************************************** ***

    What I would like to do now is to copy and paste to the destination just the values that exist in the EntireRow. I can't seem to find where in the CopyYes line to place a PasteSpecial xlValue thingy. It may be that the PasteSpecial goes on the next line. I don't know. Anyway, I just need a little more nudge.

    Thanks again.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    try:-

    <pre>Sub CopyItems()

    Dim CopyYes As Range

    For Each CopyYes In Range("I38:I200")
    If CopyYes.Value = "y" Then
    CopyYes.EntireRow.Copy
    Sheet2.Range("A65536:J65536").End(xlUp).Offset(1). PasteSpecial xlPastevalues
    End If
    Next

    End Sub

    </pre>


  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    Hi, Adam. That worked perfectly. Thanks so much.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't know what to call it (Excel 97, SR2)

    Hi, Adam. You provided VBA code similar to what is below and it worked for my purpose at the time.

    Sub CopyItems()
    Dim CopyYes As Range

    Sheets("CopyTo").Range("A7:FA220").ClearContents

    For Each CopyYes In Range("H1:H211")

    If CopyYes.Value = "y" Or CopyYes.Value = "Y" Or CopyYes.Value = "Yes" _
    Or CopyYes.Value = "yes" Or CopyYes.Value = "YES" Then
    CopyYes.EntireRow.Copy
    Sheets("CopyTo").Range("A65536:FA65536").End(xlUp) .Offset(1).PasteSpecial Paste:=xlAll
    End If

    Next
    End Sub

    I now need to be able to find a "Yes" value in the Range("H1:H211"), but I only need to copy about six non-contiguous cells to another worksheet. The CopyYes.EntireRow.Copy doesn't work for what I need. I have tried CopyYes.Range("A1, D1:G1, B1").Copy, but, of course, that doesn't work. Can this be done? If you need more info, please ask. TIA

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

    Re: Don't know what to call it (Excel 97, SR2)

    Try something like this:

    <pre>Dim lRow As Long
    lRow = CopyYes.Row
    CopyYes.Range("A" & lRow & ":B" & lRow & ",D" & lRow & ":G" & lRow).Copy
    </pre>

    Legare Coleman

Posting Permissions

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