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

Topic

New Reply

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.

Reply | Quote

Replies

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).Offset(1)
End If
Next

End Sub

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

HTH

graeme

Reply | Quote

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(xlUp).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.

Reply | Quote

try:-

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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:-

Range("myrange").AdvancedFilter xlFilterCopy, Range("criteria"), Range("targetrange")

Reply | Quote

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

Reply | Quote

Try something like this:

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

Reply | Quote