PDA

View Full Version : Copy and paste data from one worksheet to another with a unique criteria



simplemanny
07-10-2015, 08:46 AM
- I have two worksheets namely “profile types” and “data input”.
- I wish to have a user enter a unique number in cell F4 of the data input worksheet.
- The “profile types” worksheet is then opened, where the unique number entered is cross checked with the numbers present in
cells from ranging from B3 to K3.
- When the same number has been found in the range of cells, on the column in which the unique number was found. Data is then copied from the range of cells with rows 5 to 79. Thus, same column but varying rows from 5 to 79. Example: x5,x6,x7,…x79 are all copied.
- The “data input” worksheet is then opened up and the data copied is then pasted on cells B16 to B90

mancubus
07-11-2015, 02:05 PM
must be called by user:
copy to a standard code module.



Sub vbax_53149_Find_Copy()

Dim ColNum As Long

On Error GoTo NotFound

With Worksheets("profile types")
ColNum = .Range("B3:K3").Find(Worksheets("data input").Range("F4").Value).Column
Worksheets("data input").Range("B16:B90").Value = .Range(.Cells(5, ColNum), .Cells(79, ColNum)).Value
End With

Exit Sub

NotFound:
MsgBox "Criteria not found in Worksheet 'profile types', Range 'B3:K3'"

End Sub

mancubus
07-11-2015, 02:11 PM
triggered by entering a value in F4.
copy to "data input" sheet's code module.



Private Sub Worksheet_Change(ByVal Target As Range)

Dim ColNum As Long

If Target.Address <> "$F$4" Then Exit Sub

On Error GoTo NotFound

With Worksheets("profile types")
ColNum = .Range("B3:K3").Find(Worksheets("data input").Range("F4").Value).Column
Worksheets("data input").Range("B16:B90").Value = .Range(.Cells(5, ColNum), .Cells(79, ColNum)).Value
End With

Exit Sub

NotFound:
MsgBox "Criteria not found in Worksheet 'profile types', Range 'B3:K3'"

End Sub

simplemanny
07-13-2015, 05:48 AM
thank you very much, it works

simplemanny
07-13-2015, 05:50 AM
could you also help with this? Also i was a macro to solve the problem is attacted, i have problems with it, please view. Thanks for the assistance.

p45cal
07-13-2015, 07:57 AM
cross posted:
http://www.vbforums.com/showthread.php?800061-Copy-and-paste-data-from-one-worksheet-to-another-with-a-unique-criteria
http://www.excelforum.com/excel-programming-vba-macros/1093009-copy-and-paste-data-from-one-worksheet-to-another-with-a-unique-criteria.html