Monday, May 14, 2007

Dynamic Copy of Matching Excel Data

Copy specific data in cells from Master Sheet to Current Sheet

Most often we would have the entire data in Excel and would require data corresponding to the cell value taken from the master sheet and populated in the current one dynamically.

In the following example the master sheet is named as "DB" and contains all records with the primary key being the first column.

Function Snippet_For_Copy(sSearchString)

If Trim(sSearchString) = "" Then Exit Function

With Sheets("DB").Columns("A:A")
Set rFindCell = .Find(sSearchString, LookIn:=xlValues, LookAt:=xlWhole)
If Not rFindCell Is Nothing Then
Sheets("DB").Rows(rFindCell.Row).EntireRow.Copy _
Destination:=Range("A" & ActiveCell.Row)
End If
End With

End Function

If the user enters a data in the first column of the current sheet, the above function will check the data in the DB sheet and transfer entire row if a match is found

You can trigger the function using Worksheet_SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If IsNumeric(Target) = False Then Exit Sub
If Trim(Target) = "" Then Exit Sub

Application.EnableEvents = False

Snippet_For_Copy Target.Value

Application.EnableEvents = True
End Sub





Computers blogs

1 comment:

  1. I have a problem that is similar to what you have defined above but instead of copying entire row, I want to copy and paste only selection of data.

    What I want to do is check if a KeyID I have entered in main spreadsheet(where I am entering the Macro) also exists in 3 different workbooks. If it does, then I want to pull various data from these 3 books into the main book. Also, the names of the 3 books change monthy (no there is no way to keep them static) so i was thinking if there is a way when I run macro, it will ask me what 3 files to check.
    May be this will help:

    "Main" Book where Macro is running from:
    Key ID>>>Count>>>Type>>>Month>>>Age>>>Price>>>Color
    1>>>>>>>
    3>>>>>>>
    5>>>>>>>

    Book1 (Wanted data is ALWAYS in column C)
    KeyID>>>ColumnA>>>ColumnB>>>Count>>>ColumnD
    1>>>>>>xxxxxxxx>>>xxxxxxxx>>>15000>>>xxxxxxxx
    2>>>>>>xxxxxxxx>>>xxxxxxxx>>>08000>>>xxxxxxxx
    3>>>>>>xxxxxxxx>>>xxxxxxxx>>>20000>>>xxxxxxxx
    4>>>>>>xxxxxxxx>>>xxxxxxxx>>>15000>>>xxxxxxxx
    5>>>>>>xxxxxxxx>>>xxxxxxxx>>>19870>>>xxxxxxxx

    Book2 (Wanted Data is ALWAYS in column A and D)
    KeyID>>>Type>>>ColumnB>>>ColumnC>>>Month
    1>>>>>>...L...>>>xxxxxxxx>>>xxxxx>>> May
    2>>>>>>...S...>>>xxxxxxxx>>>xxxxx>>> June
    3>>>>>>...M....>>>xxxxxxxx>>>xxxxx>>> Aug
    4>>>>>>...S....>>>xxxxxxxx>>>xxxxx>>> Jan
    5>>>>>>....M...>>>xxxxxxxx>>>xxxxx>>> Feb

    Book3 (Wanted Data is Always in a Range)
    KeyID>>>ColumnA>>>Age>>>Price>>>Color>>>ColumnE
    1>>>>>>xxxxxxxx>>>..3..>>>.050>>>Black>>>xxxxxxxx
    2>>>>>>xxxxxxxx>>>..9..>>>.090>>>Black>>>xxxxxxxx
    3>>>>>>xxxxxxxx>>>..5..>>>.070>>>Red.>>>xxxxxxxx
    4>>>>>>xxxxxxxx>>>..2..>>>.030>>>Orng>>>xxxxxxxx
    5>>>>>>xxxxxxxx>>>..1..>>>010>>>White>>>xxxxxxxx

    As such, the result should be:
    "Main" Book where Macro is running from:
    Key ID>>>Count>>>Type>>>Month>>>Age>>>Price>>>Color
    1>>>>>>>1500>>>>..L..>>>May>>>>>..3..>>>.050>>>Black
    3>>>>>>>2000>>>>..M..>>>Aug>>>>>..5...>>>.070>>>Red
    5>>>>>>>19870>>>..M..>>>Feb.>>>>>..1....>>>010>>>White

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group