copying all details of scanned tags


Board Regular
Dec 18, 2011
Please help me to make a program using excel which I can use in my current work. As you can see I have a series of inputs from column A to column F. What I want to happen is when I input a tag or series of tags on column H starting at H3, it will scan all tags at column C and all similar tags will copied together with their corresponding details to column J to column O. Thank you. See below sample.

1Work orderWork DescriptionTagAreaRequire Bypass?Shutdown Input Tag HERE! Work orderWork DescriptionTagAreaRequire Bypass?Shutdown
360166Inspetion of MixerPE-Y-7601P1yesyes PE-Y-7609 60174Declogging of linesPE-Y-7609P3yesno
460170Inspetion of BeltPE-Y-7607P2yesno PE-Y-7730 60182Cleaning of filterPE-Y-7730P5nono
560174Declogging of linesPE-Y-7609P3yesno PP-Y-7772 60186Replacement of ValvesPP-Y-7001P4yesyes
660178Manual Barring of compressorPE-Y-7610AP4noyes PP-Y-7001
760182Cleaning of filterPE-Y-7730P5nono PP-Y-7202
860186Replacement of ValvesPP-Y-7001P4yesyes
960190Sampling of VesselsPP-Y-7002P5noyes

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>


Well-known Member
Mar 11, 2015
If there is more than one match then this will NOT work
(finds first match only)

In J3 copied down


To suppress error if match not found

Last edited:


Well-known Member
Jun 29, 2014
Hello imr,

Here is a VBA solution that may work for you:-

Sub Test()
        Dim ar As Variant, lr As Long
        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        ar = Sheet1.Range("H3", Sheet1.Range("H" & Sheet1.Rows.Count).End(xlUp))

Application.ScreenUpdating = False

        Sheet1.Range("J3:O" & lr).Clear

If IsArray(ar) Then
For i = 1 To UBound(ar)
        Sheet1.Range("C1", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar(i, 1)
         Sheet1.Range("A2", Sheet1.Range("F" &  Sheet1.Rows.Count).End(xlUp)).Copy Sheet3.Range("J" &  Rows.Count).End(3)(2)
        Sheet3.UsedRange.Copy Sheet1.[J3]
Next i
        Sheet1.Range("C1", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp)).AutoFilter 1, ar
         Sheet1.Range("A2", Sheet1.Range("F" &  Sheet1.Rows.Count).End(xlUp)).Copy Sheet3.Range("J" &  Rows.Count).End(3)(2)
        Sheet3.UsedRange.Copy Sheet1.[J3]
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
The code places the tag numbers that you enter in Column H into an array then filters Column C for the array values and copies the relevant rows of data (from Columns A:F) over to Columns J:O.
The code also covers the fact that there may only be one element in the array every now and then.
The code also uses a helper sheet (Sheet3 in this case).

You may also note that I have used the sheet codes (Sheet1, Sheet3 etc.) in the above code.
Is there any reason for not using Row2?

I've uploaded a little sample file at the following link for you to play with:-***1r2

Click on the "RUN" button to see it work. Alter the amount of tag numbers in Column H to see how the code adjusts.

I hope that this helps.


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...