Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the function opposite of LOG | Excel Worksheet Functions | |||
What is the function opposite of LOG | Excel Worksheet Functions | |||
opposite of intersect | Excel Programming | |||
Opposite of CONCATENATE | Excel Programming | |||
Opposite of Auto_Open | Excel Programming |