Requesting some help with print macros

[currong]

I have a stores order spreadsheet with >200 line items. When ordering items I may only require say 9 of the items from the total list. Therefore when I print the order I only want the rows that have a qty in column E. I have adapted a macro from an online source to print only the selected items. The only items that are printed are those with a value in col E, however, I get 4 pages of report with the footer printed. The print does not stop at the end of the list of items with a val in col E. What do I need to change on the macro to stop the waste of printed paper.

Sub PrintOrderCosting()
'prints rows of data, will not print rows if column E is blank
Application.ScreenUpdating = False
Range("E13:E250").EntireRow.Hidden = False
Range("E13:E250").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("E13:E250").EntireRow.Hidden = False
Application.ScreenUpdating = True

Range("a1:h250").Select

ActiveSheet.PageSetup.PrintArea = "$a$1:$h$250"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$a$1:$h$250"
Application.PrintCommunication = False
Application.ScreenUpdating = True
ActiveWorkbook.RefreshAll
End Sub

 

[dnorm]

Hi Currong

I would firstly add a filter across your data, then you can add a VBA to filter out "0" results and print the remainder.

Try This:

Sub Filter_Print()
'
' Adds a filter then filters current orders
' Prints range

'
Range("A12").Select
Selection.AutoFilter
ActiveSheet.Range("$A$13:$J$5900").AutoFilter Field:=5, Criteria1:="<>"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("a1:h250").Select

ActiveSheet.PageSetup.PrintArea = "$a$1:$h$250"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$a$1:$h$250"
Application.PrintCommunication = False
Application.ScreenUpdating = True

ActiveSheet.Range("$A$13:$J$5900").AutoFilter Field:=5
ActiveWorkbook.RefreshAll

End Sub

I hope this works, still learning myself.

 

[currong]

Thank you for the reply and suggested solution to my problem. The print function is now only printing on the number of pages required to display the information without wasting paper.

I have another issue I am hoping can be resolved.

Column H is where an item qty is entered for the purpose of ordering stores. When the order is placed, I wish to record the qty from a specific date to a specific date, this will show how much has been ordered for the year to date. After the order is filled, I will clear all qty in col H and ready the col H to be used for the next order. How can I retain the qty ordered each time and record a YTD per row. Hope this makes sense.

 

[dnorm]

Hi currong

Sorry I have not replied sooner, So what you are asking is:
Orders placed / Recorded
A copy of orders stored (on a Record Sheet for example) year to date information
Order information cleared ready for the next one.

Can you tell me what you would like to call the worksheet/ workbook where this data will be stored?

I can look into it. Hopefully I will have something tomorrow.

 

[currong]

I can work with any name for the worksheet, but lets use Register to keep the syntax simple. Your time is valued and appreciated, so thank you.