Thread: 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

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
>

=?Utf-8?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