Find data on separate worksheet

[RMoore2764]

I am working on a macro where I am searching for data in a separat
worksheet that matches the data in a particular column on my origna
worksheet. Any assistance would be greatly appreciated.

Details:

Column N of 'ORIG SHEET' contains my unique identifier 'ACCT'. I a
trying to search 'NEW SHEET' to see if it contains the selected 'ACCT'
There will not always be a match. If there is a match I want to cop
columns C thru L from 'NEW SHEET' and paste it beginning at column X o
'ORIG SHEET'.

A few final notes: each 'ACCT' will only be listed once on 'ORI
SHEET' will not be on multiple rows. Each worksheet contains betwee
500 and 1000 rows.

Thanks for any help

 

[pikus]

Will the "ACCT" information be in any particular column on the Ne
Sheet? - Piku

 

[RMoore2764]

Yes, 'ACCT' will always be in column E on NEW SHEET.

Thanks for your repl

 

[pikus]

lrowORIG = Worksheets("ORIG SHEET").UsedRange.Row - 1 + Worksheets("ORI
SHEET").UsedRange.Rows.Count
lrowNEW = Worksheets("NEW SHEET").UsedRange.Row - 1 + Worksheets("NE
SHEET").UsedRange.Rows.Count

For x = 1 To lrowORIG
acct = Worksheets("ORIG SHEET").Cells(x, 14).Value
For z = 1 To lrowNEW
If Worksheets("NEW SHEET").Cells(z, 5).Value = acct Then
For n = 3 To 12
Worksheets("ORIG SHEET").Cells(x, n + 21).Value = _
Worksheets("NEW SHEET").Cells(z, n).Value
Next n
End If
Next z
Next x

I didn't actually test this so just let me know if it doesn't work.
Also, I want to make sure you know that I didn't make it capy and past
the cells themselves, just the values. I assumed this would be okay
but if it's not let me know.

- Piku

 

[pikus]

I almost forgot... Given that you're dealing with so many records
you'll save alot of time if you put this at the top of the code:

Application.ScreenUpdating = False

And at the end of the code:

Application.ScreenUpdating = True

Also, if you're certain that the account numbers are always going to b
in alphabetical/numerical order, you could use a binary search whic
would cut the amount of time it takes to run even further (A LOT!) bu
it would only work if they're in order.

- Piku

 

[RMoore2764]

Pikus,

Thanks for the replies. You have been most helpful. What yo
suggested works as I had hoped with one exception. On 'NEW SHEET
column L contains comments which are not brought over to 'ORIG SHEET
during process. Do you know of a way to bring them ove
(copy/paste?)??. Thanks again.

Ro

 

[pikus]

lrowORIG = Worksheets("ORIG SHEET").UsedRange.Row - 1 + Worksheets("ORI
SHEET").UsedRange.Rows.Count
lrowNEW = Worksheets("NEW SHEET").UsedRange.Row - 1 + Worksheets("NE
SHEET").UsedRange.Rows.Count

For x = 1 To lrowORIG
acct = Worksheets("ORIG SHEET").Cells(x, 14).Value
For z = 1 To lrowNEW
If Worksheets("NEW SHEET").Cells(z, 5).Value = acct Then
For n = 3 To 12
Worksheets("NEW SHEET").Range(.Cells(z, n)).Copy Destination
Worksheets("ORIG SHEET").Range(.Cells(x, n + 21))
Next n
End If
Next z
Next x

Just for kicks, give this a try. Again I haven't tested it, but i
theory it should work... - Piku

 

[RMoore2764]

When I try to run I get an 'Invalid or Unqualified Reference' error
message and the second '.Cells' on the following line is highlighted.

Worksheets("NEW SHEET").Range(.Cells(z, n)).Copy Destination:=
Worksheets("ORIG SHEET").Range(.Cells(x, n + 21))

And I am just stumped!!

 

[pikus]

Try Worksheets("NEW SHEET").Range(.Cells(z, n)).Copy Destination:
Worksheets("ORIG SHEET").Cells(x, n + 21

 

[RMoore2764]

I had to make a minor change to your suggestion to get it to work. Her
it is:

Worksheets("NEW SHEET").Cells(z, n).Copy Destination:= Worksheets("ORI
SHEET").Cells(x, n + 21)

Again, Thanks for all your help today