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.

[TABLE="width: 1042"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]MEL0976[/TD]
[TD]BSS[/TD]
[TD]S. Ghazanfar Ali[/TD]
[TD]Vehicle, Labour & Waiting Charges[/TD]
[/TR]
[TR]
[TD]BER6132[/TD]
[TD]BSS[/TD]
[TD]S. Ghazanfar Ali[/TD]
[TD]Vehicle, Labour & Waiting Charges[/TD]
[/TR]
[TR]
[TD]BLN6260[/TD]
[TD]BSS[/TD]
[TD]S. Ghazanfar Ali[/TD]
[TD]Vehicle, Labour & Waiting Charges[/TD]
[/TR]
[TR]
[TD]BGI7133[/TD]
[TD]BSS[/TD]
[TD]S. Ghazanfar Ali[/TD]
[TD]Vehicle, Labour & Waiting Charges[/TD]
[/TR]
[TR]
[TD]BLN6260[/TD]
[TD]BSS[/TD]
[TD]S. Ghazanfar Ali[/TD]
[TD]Vehicle, Labour & Waiting Charges[/TD]
[/TR]
[TR]
[TD]BER6132[/TD]
[TD]BSS[/TD]
[TD]Abdullah & Brothers[/TD]
[TD]In-Door-3[/TD]
[/TR]
[TR]
[TD]BER6132[/TD]
[TD]BSS[/TD]
[TD]Abdullah & Brothers[/TD]
[TD]Transportation Shifting RT ZTE Sites to Karachi W/H[/TD]
[/TR]
[TR]
[TD]MEL0976[/TD]
[TD]BSS[/TD]
[TD]Ahad Installation Network[/TD]
[TD]Feeder Dismentalling, Including Tax[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 549"]
<tbody>[TR]
[/TR]
[TR]
[TD]

and i need in this form.[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 1042"]
<tbody>[TR]
[TD]BER6132
[/TD]
[TD]BSS[/TD]
[TD]S. Ghazanfar Ali[/TD]
[TD]Vehicle, Labour & Waiting Charges[/TD]
[/TR]
[TR]
[TD]BER6132[/TD]
[TD]BSS[/TD]
[TD]Abdullah & Brothers[/TD]
[TD]In-Door-3[/TD]
[/TR]
[TR]
[TD]BER6132[/TD]
[TD]BSS[/TD]
[TD]Abdullah & Brothers[/TD]
[TD]Transportation Shifting RT ZTE Sites to Karachi W/H[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 549"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.