Find and copy

lobo11 · #1 01-23-2010, 03:54 PM

I have a list of ID numbers listed in a column on one worksheet. I want to find a subset of these ID numbers in another worksheet, copy the entire row associated with the ID number and paste the row it into yet another worksheet.

This is my first attempt at writing some vba code and any help would be greatly appreciated as I really don't want to do all this by hand.

Here is what I have have tried so far:

Code:

Sub copypaste()

Dim mySelect As String

    ActiveCell.Select
    mySelect = Selection.copy
    Windows("file.xls").Activate
    Columns("C:C").Select
    Selection.Find(What:="mySelect", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    EntireRow.Select
    Application.CutCopyMode = False
    Selection.copy
    Windows("file").Activate
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
End Sub

**Richard Buttrey** · #2 01-23-2010, 04:31 PM

Hi,

Try something like the following. Adjust the sheet references as necessary. It assumes you start on Sheet1 with a value in the Active Cell. It looks to sheet2 for that value in col C and pastes the whole row back in sheet1. Note that .Select and .Activate are rarely ever needed in code. they only slow down the execution and get in the way. After you've used the macro recorder which always captures these instructions just edit and remove them. You can always address object directly without needing to activate them.

Code:

Sub copypaste()
   Dim stMySelect As String, iRow As Integer

   stMySelect = ActiveCell
   irow = Sheets("sheet2").Range("C:C").Find(what:=st).Row
   Sheets("sheet2").Range("C" & irow).EntireRow.Copy Destination:=Sheets("sheet1").Range("A1").End(xlDown).Offset(1, 0)

End Sub

lobo11 · #3 01-23-2010, 05:30 PM

Thanks for the help.

I have one problem however. I used your code and put in the correct sheet names and I get run-time error 1004, application-defined or object-defined error.

When I try to debug this, it says it has a problem in the last line of code (the one starting with Sheets...).

Any ideas? Also, is everything case sensitive? It won't let me capitalize one word (constantly changes my Copy to copy) and I know other programs I am familiar with are very case sensitive.

Thanks again.

**Richard Buttrey** · #4 01-23-2010, 06:12 PM

Hi,

Can you upload the workbook so I can take a look. Anonymise the data if it's sensitive.

Rgds

lobo11 · #5 01-23-2010, 06:28 PM

Here is a smaller version of made up numbers.

The first sheet has the ID numbers, the second sheet has the data. Ideally I'd like to paste the matching data to the third sheet.

Thanks again for taking the time to help.

lobo11 · #6 01-24-2010, 02:20 PM

For anyone who is interested, I was able to get it to work. Here is the not so elegant code I am using to get the job done.

Thanks for all the help, wouldn't have figured it out otherwise.

Code:

Sub copypaste()

    Dim stMySelect As String, iRow As Integer
    
    stMySelect = ActiveCell
    Sheets("Sheet2").Select
    Columns("C:C").Select
    iRow = Cells.Find(what:=stMySelect).Activate
    Rows(ActiveCell.Row).Select
    Application.CutCopyMode = False
    Selection.copy
    Windows("xxx.xls").Activate
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    ActiveSheet.Paste
        
    
End Sub

Welcome to the Excel Forum

Forum Statistics