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
Posts
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.
ReplyDeleteWhat 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