Excel Macro help needed...Searching, copying, and pasting

JuMaHo · #1 (**permalink**) 10-17-11, 14:32

Hi All...

I greatly appreciate any help that can be offered with my problem. I've been trying to work on this for a few days and haven't made much progress.

I have an excel spreadsheet with two tabs. One is titled "Input Sheet" where the user will fill in the selected fields, and the second is titled "Saved Entries". I've already put together a macro that allows the user to save the relevant fields on the Input Sheet to the next blank line on Saved Entries.

The next part gets tricky and my skills are not advanced enough to tackle it...

I need to be able to "recall" data from the Saved Entries back to the Input Sheet so that as additional information comes in, the user can easily update the record and then resave it to the "Saved Entries" tab.

I would like to set it up so that the user inputs the ID number for an entry on the Input Sheet and the macro will find the corresponding entry on the Saved Entries tab and paste it back to the Input sheet.

I've tried using the "Record Macro" tool but it doesnt allow me to search for the info that is in the ID field... it just records the actual number I use when recording it. Then, once I find the number on the Saved Entires tab, it is always copying and pasting the original data from when I recorded the macro, not the data that corresponds with the searched number... I'm guessing the record macro option won't work for me, but I'm not skilled enough to just come out and write code.

Again, thats so much for any help.

Julie

JerryDal · #2 (**permalink**) 10-22-11, 02:26

Attached is an example that might get you started in code-writing as you can see that it gives you more options than recording a macro. You run the macro that presents an input box to retrieve a specific entry from saved entries to row #2 on the input sheet, make your edits and run another macro to save the changes to the saved entries worksheet. I used command buttons to perform the retrieving and saving. The code just enters the information and does not save the workbook. The code ActiveWorkbook.Save could be added to the Save_Entry macro if you want to automatically save after the updating of each entry.

Code:

Sub Retrieve_Entry()
Dim i As Long
Dim ID_Num As String, isValid As Boolean

    ID_Num = InputBox("Enter the ID number for the entry" & vbCrLf & _
        "that you want to copy to the InputSheet worksheet.")
    
    If IsNull(ID_Num) Or ID_Num = "" Then Exit Sub
    
    i = 2
    Do While Sheets("SavedEntries").Cells(i, 1).Value <> ""
    
        If Sheets("SavedEntries").Cells(i, 1).Value = ID_Num Then
            isValid = True
            Exit Do
        End If
        i = i + 1
        
    Loop

    If isValid Then
        Application.ScreenUpdating = False
        Sheets("SavedEntries").Select
        Range("A" & i & ":F" & i).Select
        Selection.Copy
        Sheets("InputSheet").Select
        Range("A2").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A2").Select
        Application.ScreenUpdating = True
    Else
        MsgBox "No such id number:" & vbCrLf & ID_Num
    End If

End Sub

Sub Save_Entry()
Dim i As Long, targetRow As Long
Dim ID_Num As String, isValid As Boolean

    ID_Num = Sheets("InputSheet").Range("A2").Value
    
    i = 2
    Do While Sheets("SavedEntries").Cells(i, 1).Value <> ""
    
        If Sheets("SavedEntries").Cells(i, 1).Value = ID_Num Then
            isValid = True
            Exit Do
        End If
        i = i + 1
        
    Loop

    targetRow = i

    If isValid Then
        Application.ScreenUpdating = False
        Sheets("InputSheet").Range("A2:F2").Select
        Selection.Copy
        Sheets("SavedEntries").Select
        Range("A" & targetRow).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("A1").Select
        Sheets("InputSheet").Select
        Range("A1").Select
        Application.ScreenUpdating = True
        MsgBox "Entry has been updated in SavedEntries worksheet"
    Else
        MsgBox "Id number not found in SavedEntries worksheet:" & vbCrLf & ID_Num
    End If

End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech