Find Records,

[josros60]

Hi,


i have this huge spreasheet (attached) that i want to search for records and put the results temporary an new sheet but i am not good in vba, can any body help.

CARRIER ACCOUNT NUMBER ACCOUNT NAME BTN WTN AUTH_CODE AUTH.CODE.STATUS BCTEL 000071411 CENTURY 21 MOVING REAL ESTATE BC LTD 2503784344 0000000000 2503784344 Active BCTEL 000000021 CN (IT TELECOM) 2504597918 0000000000 2504597918 Active BCTEL 000000021 CN (IT TELECOM) 2504597928 0000000000 2504597928 Active BCTEL 000110709 GLOBAL PARTNERS INSTITUTE 6044484250 0000000000 6044484250 Active BCTEL 000076963 GEORGE RICHARD LOW 2505773758 0000000000 2505773758 Active BCTEL 000110024 ROYAL LEPAGE LOCATIONS WEST REALTY 2504937587 0000000000 2504937587 Active BCTEL 000151077 VILLAGE OF PORT ALICE 2502843392 0000000000 2502843392 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507701317 0000000000 2507701317 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708601 0000000000 2507708601 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708602 0000000000 2507708602 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507709157 0000000000 2507709157 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507701317 0000000000 2507701317 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708601 0000000000 2507708601 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507708602 0000000000 2507708602 Active BCTEL 000105151 ABC ALLEN BUSINESS COMMUNICATIONS 2507709157 0000000000 2507709157 Active BCTEL 000074693 HOMELIFE GLENAYRE REALTY CO LTD 6044627359 0000000000 6044627359 Active BCTEL 000163771 IKTEROS ENTERPRISES INC 6044488790 0000000000 6044488790 Active TELUS 000164717 BANCORP FINANCIAL SERVICES INC 6046082717 0000000000 6046082717 Active TELUS 000158736 ESKIMO STEEL 4034680910 0000000000 4034680910 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170542 0000000000 4034170542 Active TELUS 000158736 ESKIMO STEEL 4034680909 0000000000 4034680909 Active TELUS 000158736 ESKIMO STEEL 4034681736 0000000000 4034681736 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170513 0000000000 4034170513 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170514 0000000000 4034170514 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170515 0000000000 4034170515 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170516 0000000000 4034170516 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170517 0000000000 4034170517 Active TELUS 000158775 ASA ALLOYS INC EDMONTON 4034170564 0000000000 4034170564 Active TELUS 000158736 ESKIMO STEEL 4034639704 0000000000 4034639704 Active BCTEL 000171397 STERLING GROUP CORP 6048501299 0000000000 6048501299 Active BCTEL 000061435 CONSULATE GENERAL OF JAPAN 6046855895 0000000000 6046855895 Active BCTEL 000095108 TIDE RIP TOURS LTD. 2509283092 0000000000 2509283092 Active <tbody> </tbody>

<tbody>
</tbody>

thank you

 

[josros60]

The auth code is in table and somebody give a code I want to be able to list only that number with their related tisc_code from the list.

I tried to build a pivot table but I could not figured out.

Thank you

 

[Fluff]

Is this what you're after?

Sub CopyFltr()

   Dim Auth As String
   
   Auth = InputBox("Please enter an AuthCode")
   If Len(Auth) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:J1").AutoFilter 8, Auth
      .Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy Sheets("[COLOR=#ff0000]Temp[/COLOR]").Range("A1")
      .Range("A1").AutoFilter
   End With

End Sub

Change the sheet name in red to suit

 

[codeliftsleep]

Is this what you're after?

Sub CopyFltr()

   Dim Auth As String
   
   Auth = InputBox("Please enter an AuthCode")
   If Len(Auth) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:J1").AutoFilter 8, Auth
      .Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy Sheets("[COLOR=#ff0000]Temp[/COLOR]").Range("A1")
      .Range("A1").AutoFilter
   End With

End Sub

Change the sheet name in red to suit

What he said ?

 

[josros60]

thank you so much works, perfectly.

if is not too much to bother you, is there any to add if want to search for another cell not just auth_code and when transfer to "Temp" sheet can it give the records found count.

if can do it only, if not thanks for all your help.

 

[Fluff]

What would be the other column & search value?
Also, do you want to copy only those rows that match both values, or those rows that match either?

 

[josros60]

yes, the other will be account number, yes both values that match.

thank you,

 

[Fluff]

Try this

Sub CopyFltr()

   Dim Auth As String
   Dim Acc As String
   
   Auth = InputBox("Please enter an AuthCode")
   If Len(Auth) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If
   Acc = InputBox("Please enter an Account")
   If Len(Acc) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:J1").AutoFilter 8, Auth
      .Range("A1:J1").AutoFilter 4, Acc
      .Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy Sheets("Temp").Range("A1")
      .Range("A1").AutoFilter
   End With

End Sub

 

[mumps]

Sorry. Misposted.