Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing filtered records
Hi,
I have a worksheet called "Barcodes" that collects long lists of barcode entries. To reduce paper use for printing, I have used the "copy picture" function to create picture links to a worksheet called "Print" so that multiple columns can be printed on one page. The problem is the printout contains the maximum possible pages defined in the print area and not just the required pages. I have attached a screen shot as I'm not sure I have explained this properly The macro is as outlined below. Thankyou for your time, jvine ----------------------------------------------------------------------------- Sub Sort_Delete_Print() ' ' Sort_Delete_Print Macro ' ' This section sorts the data ' YesNo = MsgBox("This will print your current list of barcodes." & Chr(13) & "Do you want to continue?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes Application.ScreenUpdating = False ' ' This section sorts the data ' Sheets("Barcodes").Select Range("B1:C1001").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ' This section deletes duplicate data ' Sheets("Barcodes").Select Range("B2:B1001").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B2:B1001"), Unique:=True ' ' This section hides blank cells then prints the data ' Range("B2:B1001").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Hidden = True Sheets("Print").Visible = True Sheets("Print").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Print").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Barcodes").Select ActiveSheet.ShowAllData Range("B2").Select Application.ScreenUpdating = True MsgBox "Please collect your list of barcodes from the printer.", vbInformation, "Human Immunology Barcode Form Checks" Case vbNo MsgBox "Action cancelled", vbInformation, "Human Immunology Barcode Form Checks" End Select End Sub --------------------------------------------------------------- Attachment filename: screenshot.gif Download attachment: http://www.excelforum.com/attachment.php?postid=562266 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Printing filtered records
jvine,
Print from the "Barcodes" sheet. Set the print area to be the same as that area where you are hiding rows. Once you've hidden rows, they won't print, and you'll only get the unhidden rows. HTH, Bernie MS Excel MVP "jvine " wrote in message ... Hi, I have a worksheet called "Barcodes" that collects long lists of barcode entries. To reduce paper use for printing, I have used the "copy picture" function to create picture links to a worksheet called "Print" so that multiple columns can be printed on one page. The problem is the printout contains the maximum possible pages defined in the print area and not just the required pages. I have attached a screen shot as I'm not sure I have explained this properly The macro is as outlined below. Thankyou for your time, jvine -------------------------------------------------------------------------- --- Sub Sort_Delete_Print() ' ' Sort_Delete_Print Macro ' ' This section sorts the data ' YesNo = MsgBox("This will print your current list of barcodes." & Chr(13) & "Do you want to continue?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes Application.ScreenUpdating = False ' ' This section sorts the data ' Sheets("Barcodes").Select Range("B1:C1001").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ' ' This section deletes duplicate data ' Sheets("Barcodes").Select Range("B2:B1001").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range("B2:B1001"), Unique:=True ' ' This section hides blank cells then prints the data ' Range("B2:B1001").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Hidden = True Sheets("Print").Visible = True Sheets("Print").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Print").Select ActiveWindow.SelectedSheets.Visible = False Sheets("Barcodes").Select ActiveSheet.ShowAllData Range("B2").Select Application.ScreenUpdating = True MsgBox "Please collect your list of barcodes from the printer.", vbInformation, "Human Immunology Barcode Form Checks" Case vbNo MsgBox "Action cancelled", vbInformation, "Human Immunology Barcode Form Checks" End Select End Sub --------------------------------------------------------------- Attachment filename: screenshot.gif Download attachment: http://www.excelforum.com/attachment.php?postid=562266 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete the filtered records | Excel Discussion (Misc queries) | |||
Printing Filtered Excel Worksheets | Excel Discussion (Misc queries) | |||
accumulate filtered records from several sheets to one | Excel Discussion (Misc queries) | |||
returning value from filtered records | Excel Discussion (Misc queries) | |||
how do I see more than 1000 records filtered | Excel Discussion (Misc queries) |