ExcelBanter - Excel VBA - Printing filtered records

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Excel VBA - Printing filtered records (https://www.excelbanter.com/excel-programming/300010-excel-vba-printing-filtered-records.html)

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/

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/