Now I want the opposite
 

I've been using the following routine to print out my caseload from our
entire membership:

Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim c As Variant, NumPage As Long
For Each c In Range("myList")
Set FoundCell = Range("A:A").Find(What:=c)
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row FoundCell.Row Then Exit For
NumPage = NumPage + 1
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage
Next c
End Sub

Now I'd like a separate routine, PrintNotMine(), that will SKIP everyone
in my caseload and printout pages for all others.

I've been trying for hours to find the right rearrangement of lines to
accomplish that, but can't do it.

The only way I've been successful is to change the whole premise, i.e.
deleting the range for each in my caseload:

Sub PrintNotMine()
Dim FoundCell As Range, NumRows As Long, NumCols As Long, c As Variant
NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count
Application.ScreenUpdating = False
For Each c In Range("myList")
Set FoundCell = Range("A:A").Find(What:=c)
Range(FoundCell.Address).Resize(NumRows, NumCols).Delete shift:=xlUp
Next c
Application.ScreenUpdating = True
End Sub

Then I print the worksheet. But if I accidently Save the file after
running this routine, which I've done on one occasion, I've lost all the
pages for members in my caseload. Glad I had a backup.

I've also tried hiding rows when someone in my caseload is encountered,
but since I have Print_Titles, I still get a sheet printed with just
those 2 rows, wasting paper.

Surely there's a way. Any help?

--
David

Now I want the opposite
 

Sub PrintNotMine()
Dim sh as Worksheet
Dim FoundCell As Range, NumRows As Long, NumCols As Long, c As Variant
NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count
Application.ScreenUpdating = False
Activesheet.Copy After:=Worksheets(worksheets.count)
set sh = Activesheet
For Each c In Range("myList")
Set FoundCell = sh.Range("A:A").Find(What:=c)
FoundCell.Resize(NumRows, NumCols).Delete shift:=xlUp
Next c
sh.Printout
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"David" wrote:

I've been using the following routine to print out my caseload from our
entire membership:

Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim c As Variant, NumPage As Long
For Each c In Range("myList")
Set FoundCell = Range("A:A").Find(What:=c)
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row FoundCell.Row Then Exit For
NumPage = NumPage + 1
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage
Next c
End Sub

Now I'd like a separate routine, PrintNotMine(), that will SKIP everyone
in my caseload and printout pages for all others.

I've been trying for hours to find the right rearrangement of lines to
accomplish that, but can't do it.

The only way I've been successful is to change the whole premise, i.e.
deleting the range for each in my caseload:

Sub PrintNotMine()
Dim FoundCell As Range, NumRows As Long, NumCols As Long, c As Variant
NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count
Application.ScreenUpdating = False
For Each c In Range("myList")
Set FoundCell = Range("A:A").Find(What:=c)
Range(FoundCell.Address).Resize(NumRows, NumCols).Delete shift:=xlUp
Next c
Application.ScreenUpdating = True
End Sub

Then I print the worksheet. But if I accidently Save the file after
running this routine, which I've done on one occasion, I've lost all the
pages for members in my caseload. Glad I had a backup.

I've also tried hiding rows when someone in my caseload is encountered,
but since I have Print_Titles, I still get a sheet printed with just
those 2 rows, wasting paper.

Surely there's a way. Any help?

--
David

Now I want the opposite
 

?B?VG9tIE9naWx2eQ==?= wrote

Sub PrintNotMine()
Dim sh as Worksheet
Dim FoundCell As Range, NumRows As Long, NumCols As Long, c As Variant
NumRows = Range("Name_Copy").Rows.Count
NumCols = Range("Name_Copy").Columns.Count
Application.ScreenUpdating = False
Activesheet.Copy After:=Worksheets(worksheets.count)
set sh = Activesheet
For Each c In Range("myList")
Set FoundCell = sh.Range("A:A").Find(What:=c)
FoundCell.Resize(NumRows, NumCols).Delete shift:=xlUp
Next c
sh.Printout
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Oh, sure, take the easy way out! <VBG

Thanks, Tom

--
David