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 can successfully find a specific row (by reference in column A), copy the row and paste it below the original.

I would now like the ability to request information to fill in certain cells. For example.

  1. Find row 7, copies and pastes into Row 8.
  2. Then get a pop up for "Update job information" and it will then enter that into Cell G8.

This pattern is always the same, ie update column G in the rows that is pasted to.

Any help is greatly appreciated.

share|improve this question
1  
I suggest you ask VBA questions at Stack Overflow rather than Super USer if you want coding help. My flag to the Mods to move this question for you has been denied - but the lack of activity in your question is testament to it being asked in the wrong StackExchange corner. –  brettdj Mar 23 '12 at 11:16
add comment

1 Answer

As an extension to my answer to your last question, here's the macro again with the addition of a prompt for data entry.

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

            ' Prompt for input
            res = InputBox("Enter some info for Column G", "Update job information")
            If res <> "" Then
                cl.Cells(2, 7) = res
            End If
        End If
    End With
End Sub
share|improve this answer
    
Thanks again Chris @Chris Neilson. You've been an amazing help. –  Dave Mar 26 '12 at 10:52
    
@Chrisneilson, would you be able to help me with another request? I have amended your code to accept updating more than one field in my spreadsheet, but one of the fields is a name field, linked to a drop down list. Can the pop up have the drop down on it to select the name? Really appreciate your help. –  Dave Mar 26 '12 at 12:06
    
@Dave you probably should build a User Form in VBA for this –  chris neilsen Mar 26 '12 at 18:40
add comment

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.