Searching multiple worksheets and posting result in one sheet

[LEO00001]

Hi, I've been working on this at work for the past 3 days and I cannot figure this out.

I have multiple sheets each containg the portfolio of certain accounts. I want to be able to search the securities that they hold and have it sorted by the accounts.

I have a drop down list in sheet1.
The macro will look at what is in the drop down list and search through the data in all of the sheets and copy and paste the relevant information on to sheet1.

PROBLEM:
Depending on the position of the sheet within the workbook, results are duplicated. For example,

sheet2 has B. Only Sheet2 has B. Sheet2 is before Sheet3,4,5,6.
Then when I search B, it will show B 5 times.

I think it is a problem with the loop i have but I don't know how to edit it.

I will post my code.
My boss is breathing down my neck. Any help would be greatly appreciated.
------------------------------------------------------------

Sub Findholders()
'this macro is supposed to find the "Holders" for the "issuers" that we want to search for
      With Application
          .ScreenUpdating = False
          .EnableEvents = False
      End With
    'after i run the macro, i want to hide the sheets. So to run the macro, i need to make the sheets visible again
    Dim wsSheet As Worksheet
    
      For Each wsSheet In ActiveWorkbook.Worksheets
      wsSheet.Visible = xlSheetVisible
      Next wsSheet

    
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim rng As Range
    Dim rng2 As Range
    Dim j&
    Dim f&
    
    
    
    Set WS1 = Sheets(1)
    
      'the following clears the previous search's results to prepare for the new search
      Range("Holders!A6").Activate
      
      Rows("6:6").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.clearcontents
      
    'this is the loop that is causing problems
    For j = 3 To Sheets.count
    Set WS2 = Sheets(j)
      WS2.Select
      Range("A15").Autofilter Field:=3, Criteria1:="=" & WS1.Range("c3").Value
      
      
        With WS2.Autofilter.Range
          On Error Resume Next
          ' Set rng2 to rng without the header row
          Set rng2 = .Offset(1, 0).Resize(.Rows.count - 1, .Columns.count) _
                     .SpecialCells(xlCellTypeVisible)
          On Error GoTo 0
            If Not rng2 Is Nothing Then
              'Copy and paste the cells into WS2 below the existing data
              rng2.Copy
       
              With Sheets(1).Range("A" & LastRow(WS1) + 1)
               ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
                .PasteSpecial xlPasteValues
                .PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
              End With
            End If
          End With
    
    WS2.AutoFilterMode = False
    
    Next j
    'this formats the columns the way I want them to. this is not part of the problem
    WS1.Activate
    Range("P6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("B6").Select
    ActiveSheet.Paste
    Range("B3").Select
        
End Sub

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(what:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlValues, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

 

[PHV]

With WS2.Autofilter.Range
[!]Set rng2 = Nothing[/!]
On Error Resume Next


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

"...multiple sheets each containg the portfolio of certain accounts..."

This is a recipie for on-going hurt, frustration, agony and defeat!

I'd spend my efforts getting my "database" in one sheet. This IS the best and accepted practice for data storage, data analysis and data reporting. ALL of Excels data-related tools are designed to support a single data source.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[LEO00001]

I guess i'll try to do it a different way. Thanks for the advice. BUT. i would still appreciate if someone could just show me how to fix this...

 

[PHV]

Have you tried my suggestion posted 26 Jul 07 15:03 ?

 

[SkipVought]

Did you check out PHV's post? Did it not help?


FYI, with a properly designed data source, this solution could have been arrived at in less than 15 SECONDS.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[LEO00001]

I actually got it to work. THANKS ALOT. I really appreciate it guys.

 

[NorthNone]

LEO00001,
Could you post your solution?
TIA,
Northnone

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller