An Excel Macro for Pulling in Data from Another Worksheet

You may be a super hacker, but your users aren’t.  All the things you take for granted in a quick query that joins a few tables is far beyond anything they’ll ever try to take on for themselve.  Some of them are pretty good getting around in Excel, but only one in a hundred will touch something like Microsoft Access.  So they come to you for every little thing.

So, in the interests of “faking” database functionality on the Excel side of things, here is a little macro code for looking up data in another worksheet and copying it over into where you’re working.  It looks at the cell to the left of your current cell, then finds a row that matches it in a worksheet named “Data”.  The row of data is then copied back into the current cell and the 9 cells to the right, but if data is already there it does NOT overwrite it.  The scenario here is that someone sends a list of part numbers or customer numbers in an email and your manger types and power users can now have way to quickly append the usual bits of information that they need to really work with it.  Note that in this case, individual users are assumed to be maintaining their own Data worksheets.

Sub DataPull()
'
' DataPull Macro
' Copy text from the "Data" sheet based on the cell to the left of the currently active cell.
'
' Keyboard Shortcut: Ctrl+d
'
    On Error GoTo DataPullError
    Dim Find As String
    Dim Cur As String
    Dim x As Long
    Dim y As Long

    If ActiveCell.Column = "1" Then
        MsgBox "Data macro looks up based on the cell to the left of the current cell.", vbExclamation, "Data Pull macro"
        Exit Sub
    End If

    Find = ActiveCell.Previous

    Do
        x = x + 1
        Cur = Worksheets("Data").Range("A" & x).Value
        If Cur = Find Then
            For y = 0 To 9
                Cur = Worksheets("Data").Range(Chr(66 + y) & x).Value
                If ActiveCell.Next(1, y) = "" Then ActiveCell.Next(1, y) = Cur
            Next y
        End If
    Loop Until Cur = ""

    Exit Sub
DataPullError:
    MsgBox Err.Description, vbCritical, "Data Pull macro"
End Sub

Note: I tried using VLOOKUP for something like this, but performance appeared to be awful for the size of the datasets I was working with.  The “dumb” just-copy-some-data-in-here-and-don’t-worry-about-staying-in-sync approach is used here to eliminate the possibility of any lame slow-downs or program hangs that can be caused by a large number of VLOOKUP’s.