I think a good way for you to do this would be to use the find function to
return a range, and then offset that range accordingly. My first action
would be to use the account numbers in the other sheet as your reference
point. Here is some code, assuming that your account number is a3 for all of
your worksheets. I am also assuming that your lookup numbers are in column A
on your various worksheets, and the data you are importing from the data
sheet from columns are A through M (not N, since that is the account number)
will be but in B through L since you do not want to overwrite your account
numbers.

Sub DataMover()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wks As Worksheet
Dim LookupRange As Range

Worksheets("Data Worksheet").Activate

For Each wks In Worksheets

' Skip the data worksheet
If wks.Name < "Data Worksheet" Then
' Gather the lookup numbers
Set LookupRange = wks.Range("A3")

' Start cycling through the account numbers on the worksheets
For i = 0 To Range(LookupRange, LookupRange.End(xlDown)).Cells.Count
- 1
' For the columns B through M (11 columns)
k = 1
For j = -11 To -1 Step 1
LookupRange.Offset(i, k).Value = Range("L1",
Range("L1").End(xlDown)).Find(What:=LookupRange.Of fset(i, 0).Value).Offset(0,
j).Value
k = k + 1
Next j
Next i
End If
Next wks

End Sub

Hope this helps.


Mel


"WilliamVierra" wrote:


I have a spreadsheet with many worksheets in it.

One worksheet contains all the data for the other worksheets. What I
need to automate is moving the data specific to an individual worksheet
from the data worksheet to the specific worksheet.

The key for the data is an account number that is in column L of the
data work sheet. The data preceding it in the row is what I need to
move.

I have been using OFFSET with Match with no real success. The columns
in all the worksheets are identical. The target worksheets contain the
account number in cell a3.

Typically there are 1,500 to 2,000 rows of data in the data worksheet.

So I need to search down column L in the data worksheet until I find
the matching account and copy the data in columns A through L into the
same columns in the target worksheet.

The search then needs to be preformed again for the next row until all
the rows for that account are received.


--
WilliamVierra
------------------------------------------------------------------------
WilliamVierra's Profile: http://www.excelforum.com/member.php...o&userid=26107
View this thread: http://www.excelforum.com/showthread...hreadid=395492