Hi Bourbon,

You described your data listing well, but it sounds wierd,
let me know if I misunderstood.

Put this code in the worksheet module where you want the
functionality created.

Private Sub Worksheet_Change(ByVal Target As Range)
' Identify which cell was changed.

Select Case Target.Address
Case "$M$28"
Call subRetrieveData(Target)
Case "$N$28"
Call subRetrieveData(Target)
End Select
End Sub
Private Sub subRetrieveData(rngTarget As Range)
' This procedure will paste the appropriate rows of
data
' to the cell that called it.
If rngTarget.Value = "" Then
Call subClear(rngTarget)
Exit Sub
End If

Dim shtCurrent As Worksheet
Set shtCurrent = rngTarget.Worksheet

Dim rngCompanyNumbers As Range
Set rngCompanyNumbers = shtCurrent.Range("C28", "L28")

Dim rngCurrentColumn As Range
Set rngCurrentColumn = rngCompanyNumbers.Find
(rngTarget.Value, , xlValues, xlWhole)
If rngCurrentColumn Is Nothing Then
MsgBox "Company not found."
Call subClear(rngTarget)
Exit Sub
End If

Dim rngSourceData As Range
Set rngSourceData = shtCurrent.Range
(rngCurrentColumn.Offset(2, 0).Address,
rngCurrentColumn.Offset(40, 0).Address)

Dim rngPasteTarget As Range
Set rngPasteTarget = rngTarget.Offset(2, 0)
rngSourceData.Copy
rngPasteTarget.PasteSpecial xlPasteAll

rngTarget.Select
End Sub
Private Sub subClear(rngTarget As Range)
' This procedure will clear the column's data.
Dim shtCurrent As Worksheet
Set shtCurrent = rngTarget.Worksheet

Dim rngPasteTarget As Range
Set rngPasteTarget = shtCurrent.Range(rngTarget.Offset
(2, 0).Address, rngTarget.Offset(40, 0).Address)
rngPasteTarget.Clear
End Sub

HTH.
-Brad

-----Original Message-----
I think this is what is called an event trigger, but how
to use?
Columms C to L have data in them from rows 30 to row 68.
Each columm is
a different company (thus there are 10 companies). Each
columm is
numbered 1 to 10 (thus C has number 1 in row 28, D has
number 2 in row
28, etc)...up to number 10 in columm L row 28.

Columm M and N are empty but I want to trigger an event
in row
28....when I enter for example, number 3(in row 28M) and
number 6(in
row 28N), it will copy and paste the data from the
corresponding
columms that have does numbers in row 28 into columm M
and N...

Any thoughts?
Thanks,
B.


---
Message posted from http://www.ExcelForum.com/

.