Search, Copy, & Paste

**PaulBehr** · 02-06-2010, 06:22 PM

I'm using excel 2003 for this little project.

Here's what I would like to do. I have a worksheet with customer data sorted by column items and so each row is customer specific and I also have a worksheet with an invoice. I would like to auto populate the invoice with the customer info from data worksheet. (I'll reference the cells I need after I can get the data pasted)

I would like to build a macro that will search for the reference number I tell it and copy the row with the customer data to the invoice worksheet where I will pull each desired cell data to the invoice.

I built a macro that searchs the reference number, selects the row, and copy and pastes the row into the other worksheet. My problem is when I go to put a new reference number into my little search box I made it still searches the reference number I setup the macro with and references only the inital row I setup the macro withs data

I don't want the macro to reference a specific cell, however, the active cell. & I'm having a problem telling it to find any reference numbers besides the one I initially used when I attempted to create this little helper.

Here's what I have so far. I'm kind of a noob. All the active scroll at the end is me scrolling and pasting the macro under my invoices so I can pull data later. (i'll probably put the pulled data on a seperate worksheet so it does not mess up my invoice sheet. That way I can see what i'm pulling too so it's not all tiny after it's pasted.

Range("AM5:AS5").Select 
ActiveCell.FormulaR1C1 = "33629" 
Sheets("Sheet2").Select 
Cells.Find(What:="33629", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
False, SearchFormat:=False).Activate 
Rows("6:6").Select 
Range("C6").Activate 
Selection.Copy 
Sheets("Sheet1 (2)").Select 
ActiveWindow.SmallScroll Down:=12 
ActiveWindow.ScrollRow = 14 
ActiveWindow.ScrollRow = 15 
ActiveWindow.ScrollRow = 16 
ActiveWindow.ScrollRow = 17 
ActiveWindow.ScrollRow = 18 
ActiveWindow.ScrollRow = 19 
ActiveWindow.ScrollRow = 20 
ActiveWindow.ScrollRow = 21 
ActiveWindow.ScrollRow = 22 
ActiveWindow.ScrollRow = 23 
ActiveWindow.ScrollRow = 24 
ActiveWindow.ScrollRow = 25 
ActiveWindow.ScrollRow = 26 
ActiveWindow.ScrollRow = 27 
ActiveWindow.ScrollRow = 29 
ActiveWindow.ScrollRow = 31 
ActiveWindow.ScrollRow = 32 
ActiveWindow.ScrollRow = 33 
ActiveWindow.ScrollRow = 36 
ActiveWindow.ScrollRow = 38 
ActiveWindow.ScrollRow = 40 
ActiveWindow.ScrollRow = 42 
ActiveWindow.ScrollRow = 45 
ActiveWindow.ScrollRow = 47 
ActiveWindow.ScrollRow = 49 
ActiveWindow.ScrollRow = 51 
ActiveWindow.ScrollRow = 53 
ActiveWindow.ScrollRow = 54 
ActiveWindow.ScrollRow = 55 
ActiveWindow.ScrollRow = 56 
ActiveWindow.ScrollRow = 57 
ActiveWindow.ScrollRow = 58 
ActiveWindow.ScrollRow = 59 
ActiveWindow.ScrollRow = 60 
ActiveWindow.ScrollRow = 61 
ActiveWindow.ScrollRow = 62 
ActiveWindow.ScrollRow = 63 
ActiveWindow.ScrollRow = 64 
ActiveWindow.ScrollRow = 65 
ActiveWindow.ScrollRow = 67 
ActiveWindow.ScrollRow = 68 
ActiveWindow.ScrollRow = 69 
ActiveWindow.ScrollRow = 70 
ActiveWindow.ScrollRow = 71 
ActiveWindow.ScrollRow = 72 
ActiveWindow.ScrollRow = 73 
ActiveWindow.ScrollRow = 74 
ActiveWindow.ScrollRow = 75 
ActiveWindow.ScrollRow = 76 
ActiveWindow.ScrollRow = 77 
ActiveWindow.ScrollRow = 78 
ActiveWindow.ScrollRow = 79 
ActiveWindow.ScrollRow = 80 
ActiveWindow.ScrollRow = 82 
ActiveWindow.ScrollRow = 83 
ActiveWindow.ScrollRow = 84 
ActiveWindow.ScrollRow = 85 
ActiveWindow.ScrollRow = 87 
ActiveWindow.ScrollRow = 88 
ActiveWindow.ScrollRow = 89 
ActiveWindow.ScrollRow = 90 
ActiveWindow.ScrollRow = 91 
ActiveWindow.ScrollRow = 93 
ActiveWindow.ScrollRow = 94 
ActiveWindow.ScrollRow = 95 
ActiveWindow.ScrollRow = 96 
ActiveWindow.ScrollRow = 97 
ActiveWindow.ScrollRow = 98 
ActiveWindow.ScrollRow = 99 
ActiveWindow.ScrollRow = 100 
ActiveWindow.ScrollRow = 101 
ActiveWindow.ScrollRow = 102 
ActiveWindow.ScrollRow = 104 
ActiveWindow.ScrollRow = 106 
ActiveWindow.ScrollRow = 107 
ActiveWindow.ScrollRow = 109 
ActiveWindow.ScrollRow = 111 
ActiveWindow.ScrollRow = 112 
ActiveWindow.ScrollRow = 113 
ActiveWindow.ScrollRow = 114 
ActiveWindow.ScrollRow = 115 
ActiveWindow.ScrollRow = 117 
ActiveWindow.ScrollRow = 118 
ActiveWindow.ScrollRow = 119 
ActiveWindow.ScrollRow = 120 
ActiveWindow.ScrollRow = 122 
ActiveWindow.ScrollRow = 124 
ActiveWindow.ScrollRow = 125 
ActiveWindow.ScrollRow = 126 
ActiveWindow.ScrollRow = 127 
ActiveWindow.ScrollRow = 128 
ActiveWindow.ScrollRow = 129 
ActiveWindow.ScrollRow = 130 
ActiveWindow.ScrollRow = 131 
ActiveWindow.ScrollRow = 132 
ActiveWindow.ScrollRow = 133 
ActiveWindow.ScrollRow = 134 
ActiveWindow.ScrollRow = 135 
ActiveWindow.ScrollRow = 136 
ActiveWindow.ScrollRow = 137 
ActiveWindow.ScrollRow = 139 
ActiveWindow.ScrollRow = 141 
ActiveWindow.ScrollRow = 142 
ActiveWindow.ScrollRow = 143 
ActiveWindow.ScrollRow = 144 
ActiveWindow.ScrollRow = 145 
ActiveWindow.ScrollRow = 146 
ActiveWindow.ScrollRow = 147 
ActiveWindow.ScrollRow = 148 
ActiveWindow.ScrollRow = 149 
ActiveWindow.ScrollRow = 150 
ActiveWindow.ScrollRow = 151 
ActiveWindow.ScrollRow = 152 
ActiveWindow.ScrollRow = 153 
ActiveWindow.ScrollRow = 154 
ActiveWindow.ScrollRow = 155 
ActiveWindow.ScrollRow = 156 
ActiveWindow.ScrollRow = 157 
ActiveWindow.ScrollRow = 158 
ActiveWindow.ScrollRow = 159 
ActiveWindow.ScrollRow = 160 
Range("A194").Select 
ActiveSheet.Paste 
End Sub

I know this is strange but any tips will help me out.

Thanks

**teylyn** · 02-06-2010, 06:44 PM

Hi PaulBehr,

welcome to the forum. However, your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I will add the code tags for you this time, but I'll expect you to use them from now on.

**protonLeah** · 02-06-2010, 08:58 PM

You didn't describe any of your sheets. So, feeling around in the dark...


Option Explicit

Sub PaulBehr()
    Dim SearchVal       As String, _
        FindRange       As String, _
        LastRow         As Long, _
        lastCol         As Long, _
        CopyCell        As Range, _
        copyRange       As Range, _
        FINDBOX         As Range, _
        SearchResults
        
    Set FINDBOX = Sheets("sheet1").Range("AM5")
    
    'Get the data to search for...
    SearchVal = FINDBOX.Value
    
    'Get the last used row in column A
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    FindRange = "A1:A" & LastRow
    
    'perform the search
    Sheets("Sheet2").Activate
    With Sheets("sheet2").Range(FindRange)
         Set SearchResults = .Find(What:=SearchVal)
    End With
    
    'exit if search data not found
    If SearchResults Is Nothing Then
        Beep
        MsgBox SearchVal & "... was not found"
        Exit Sub
    End If
    
    'find the last column of data to copy
    lastCol = Sheets("Sheet2").Cells(SearchResults.Row, Columns.Count).End(xlToLeft).Column
    
    Set copyRange = Sheets("Sheet2").Range(Cells(SearchResults.Row, 1), Cells(SearchResults.Row, lastCol))
    
    'add a new sheet and name it with the search for value
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = SearchVal
    
    
    'copy the entire range of data to the newly inserted sheet in row 1
    For Each CopyCell In copyRange
        If CopyCell.Value = "" Then
            Exit Sub                    'done, quit
        Else
            ActiveCell.Value = CopyCell.Value
            ActiveCell.Offset(0, 1).Activate
        End If
    Next CopyCell
End Sub

Note: Those "activewindow.scroll" lines should have been deleted. You only needed: Range("A194").Select