1

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.

1
  • 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, 2012 at 11:16

1 Answer 1

1

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
3
  • Thanks again Chris @Chris Neilson. You've been an amazing help.
    – Dave
    Mar 26, 2012 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, 2012 at 12:06
  • @Dave you probably should build a User Form in VBA for this Mar 26, 2012 at 18:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .