VBA to Compile List from Multile Sheets.

[CreativeUsername]

Hi,

I'm stumped with the following problem.

I have a software output that is exported to Excel in a matrix with combined text and numbers. There are merged cells left right and center BUT I prefer NOT to unmerge them if possible for ease of visual analysis later. Several tabs each containing a unique matrix.

I have the following code that works fine on the output to cycle through the tabs and get a specific value from a specific location and create a list. Some how its backfiring and grabbing erroneous values. Partially because of the variable location of the actual data but in some cases the printed results make no sense.

My original code grabs a 16 digit number from a specific cell and works.

Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    Sheets("Accts").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Accts" Then
            ws.Range("E9").Copy
            ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
        End If
    Next ws
     
    Application.ScreenUpdating = True
END Sub [code]

In the new output I receive the number I'm looking for will be in column B between values 5 and 20 (actually less than that range but it shouldn't matter).

After I get this section working there are other column ranges I need to search and return values in the offset 3 right location... 
My code looks like this:

[code]Sub Compile()
Dim ws As Worksheet
     
    Application.ScreenUpdating = False
    Sheets("Trends").Activate
     
    For Each ws In Worksheets
        If ws.Name <> "Trends" Or ws.Name <> "All 2011" Then '<- skip these tabs
        
        ws.Range("B8:B15").Find "ACCT NUM:", LookIn:=xlValues '<- find ACCT NUM Cell in that range
        ActiveCell.Offset(0, 3).Copy '<-- move over 3 cells and copy cell
            
            ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues '<- paste value only at bottom of list.
            
            End If
    Next ws
    
Application.ScreenUpdating = True
End Sub [code]