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.

Advertisements

One Response to “An Excel Macro for Pulling in Data from Another Worksheet”

  1. shayan khan Says:

    Hi,
    Im i doing my internship in Acer computers and im asked to make a tool for logistics concerning excel and vb. I will be grateful if u cn help me with this project. IF u can ,reply….i wll tell u abt the project.plz help me.
    thnk u

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: