Using a Loop to find Numbers in a List

Posted by Douglas on March 19, 2001 11:47 AM

I have a list I use for lookups. One column has SS #'s, and these #'s can be repeated 1 to 20 times. I'm trying to build code that will start at the top, find the first SSNO, copy and paste it to a sheet, print the sheet, then go back to the list and find the next SS# in the list that is different, copy/paste/print, on to the next SS#.
Ex. SSNO Data
882-65-84xx 564.65
882-65-84xx 23.65
717-xx-59xx 45.65
I want to choose the first, copy/paste/print, then choose the "third" and do the same, then continue on to end of the list (All repeated SS#'s are sorted in order.)

Posted by David Hawley on March 19, 2001 4:31 PM

Hi Douglas

Not too sure i have understood you, but give this code a go. it assumes your SS# are in column B of each worksheet.

I have not tested the code, so I apologise in advance fo any errors. It should give you some ideas at least.

Sub Trythis()
Dim SSnums As Range, Cell As Range
Dim sht As Worksheet, i As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

On Error Resume Next
Sheets.Add().Name = "PrintSheet"
If ActiveSheet.Name &LT;&GT; "PrintSheet" Then ActiveSheet.Delete
On Error GoTo 0

i = 0
For Each sht In ThisWorkbook.Worksheets
If sht.Name &LT;&GT; "PrintSheet" Then
Set SSnums = sht.Columns(2).SpecialCells(xlCellTypeConstants)
SSnums.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

If i = 0 Then
SSnums.Rows(1).Copy Destination:= _
Sheets("PrintSheet").Cells(1, 1)
End If
i = i + 1

Set SSnums = SSnums.Offset(1, 0).SpecialCells(xlCellTypeVisible)

For Each Cell In SSnums
SSnums.EntireRow.Copy _
Destination:=Sheets("PrintSheet").Cells(2, 1)
Sheets("PrintSheet").PrintOut
Sheets("PrintSheet").UsedRange.Offset(1, 0).Clear
sht.ShowAllData
Next Cell

End If
Next sht
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Dave

OzGrid Business Applications

Posted by Douglas on March 20, 2001 11:22 AM

Thanks Dave. I eventually found a solution that was a little simpler, but I did incorporate some of your suggestions.