Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

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

    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

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •