Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

i would like to create a macro to find a job (use find function) and then copy the row that the job is on and copy and paste the whole row including values and formulae.

The job number to find will vary each time, so i'd like a pop up box to appear that i can fill in and will fail if no matches occur.

Can any one help?

share|improve this question
    
can you share this excel file? –  Pinger Mar 19 '12 at 16:18
    
copy and paste it.. where? Your question is unclear, perhaps you could provide specific examples. –  Raystafarian Mar 19 '12 at 16:21
    
I have a job no. typed into column A, with lots of details in columns B-Z. I'd like to be able to search for a job no, and once found the macro would copy the whole row and paste it or insert it in the row below the cell that was found. Not sure i can share the file, but will try if that doesnt make sense. thanks for your help. –  Dave Mar 19 '12 at 16:31

1 Answer 1

up vote 1 down vote accepted

Here's a sample VBA macro to get you started. Comments in the code to describe the intent. You may want to add error handling to suit your needs.

Sub CopyData()
    Dim res As String
    Dim cl As Range
    Dim sh As Worksheet

    ' operate on the active sheet
    Set sh = ActiveSheet

    ' ask for ID to find in column A
    res = InputBox("Enter ID to Find", "Copy Row")

    ' If no responce, exit
    If res = "" Then
        Exit Sub
    End If

    With sh
        ' Find first occurance
        Application.FindFormat.Clear
        Set cl = .Columns(1).Find(What:=res, _
            After:=.Cells(.Rows.Count, 1), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=True)

        If Not cl Is Nothing Then
            ' if found, select entire row
            Set cl = cl.EntireRow
            ' copy and insert paste data into next row
            cl.Copy
            cl.Offset(1, 0).Insert
            ' turn off copy highlight (moving border)
            Application.CutCopyMode = False
        End If
    End With
End Sub
share|improve this answer
    
Thanks Chris @chris neilsen, just enterred that and worked straight away. Very impressed! Thank you –  Dave Mar 20 '12 at 9:14
    
@Dave your welcome, and welcome to SuperUser. Site protocol is that if an answer solves your problem, you should acept it (click the tick) –  chris neilsen Mar 20 '12 at 9:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.