Kindly Help in Solving this Excel Problem

[yousufsaleem]

Kindly help me solving searching problem in excel.

I have to copy and past following detail from one sheet to another.

For Example
i have list of 1200 sites with name and description,numbers and other details in next columns
A1 to A1200 Sites IDs
B2 to B1200 other info.......

i have to take out 100 selected sites + there detail along in separate sheet

moreover many sites are repeated in sheet with different detail.
i want all detail against each site.

consider the following excel data.

MEL0976	BSS	S. Ghazanfar Ali	Vehicle, Labour & Waiting Charges
BER6132	BSS	S. Ghazanfar Ali	Vehicle, Labour & Waiting Charges
BLN6260	BSS	S. Ghazanfar Ali	Vehicle, Labour & Waiting Charges
BGI7133	BSS	S. Ghazanfar Ali	Vehicle, Labour & Waiting Charges
BLN6260	BSS	S. Ghazanfar Ali	Vehicle, Labour & Waiting Charges
BER6132	BSS	Abdullah & Brothers	In-Door-3
BER6132	BSS	Abdullah & Brothers	Transportation Shifting RT ZTE Sites to Karachi W/H
MEL0976	BSS	Ahad Installation Network	Feeder Dismentalling, Including Tax

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

and i need in this form.
BER6132 BSS S. Ghazanfar Ali Vehicle, Labour & Waiting Charges BER6132 BSS Abdullah & Brothers In-Door-3 BER6132 BSS Abdullah & Brothers Transportation Shifting RT ZTE Sites to Karachi W/H <tbody> </tbody>
<tbody> </tbody>

<tbody>
</tbody>

 

[JLGWhiz]

Is this what you want?

Sub getSites()
Dim sh As Worksheet, lr As Long, c As Range
Set sh = ActiveSheet 'Change ActiveSheet to actual sheet, eg. Sheets("Data")
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
sh.Range("A1:A" & lr).AdvancedFilter xlFilterCopy, , sh.Range("A" & lr + 2), True
    For Each c In sh.Range("A" & lr + 2, sh.Cells(Rows.Count, 1).End(xlUp))
        Sheets.Add After:=Sheets(Sheets.Count)
        sh.UsedRange.AutoFilter 1, c.Value
        sh.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Copy ActiveSheet.Range("A2")
        sh.AutoFilterMode = False
    Next
End Sub

the code should be copied to the standard code module 1. Be sure your workbook is saved as a macroenabled workbook.