printing macro for multiple sheets with variables

Question

I have a workbook with multiple worksheets

40 of the pages are for each customer with orders for each day of the week.

I am after the macro to print only the pages that have orders in the column for tomorrows date.

I want to be able to print these as a run sheet as such.

hence if htere are no orders for the next day do not print that customers worksheet.

I also only need it to print in the range of a1:p51 for the ones that do have orders for the next day.

Answer 1

OK, got it now.  Sticking to the  issue of the formula you need, you're kind of screwed since the text in Column A of Bakery Orders does not match the text in column A in the order sheet (and it's split into two columns), so you're going to have to do some manual editing of this formula.

I think the easiest method to use is SUMIF.  In "Order Sheets" cell B11, put:

=SUMIF('Bakery Orders'!$E$5:$O$5,'Order Sheets'!$B$7+1,'Bakery Orders'!$E8:$O8)

As you copy this first across to the Uncut column and down to the other rows on "Order Sheets" you will need to manually update the row in the last part of the formula.  I'll explain what each bit does so you fully understand it;

'Bakery Orders'!$E$5:$O$5   is the range of dates to be searched for a match with tomorrow's date.  This range is constant for all cells on "Order Sheets".

'Order Sheets'!$B$7+1   is tomorrow's date (assuming B7 contains today's date).  This is what will be searched for in the first range.  This range is constant for all cells on "Order Sheets".

'Bakery Orders'!$E8:$O8   is the row containing the data you wat to return.   So for "Order Sheet" cell D11, you would put       'Bakery Orders'!$E9:$O9   because row 9 on the Bakery Orders sheet has the 900g Uncut ammount for white bread.

Do this for all cells in "Orders Sheet".  You should end up with cell B22 (or D22 if rye bread is uncut???) containing:

=SUMIF('Bakery Orders'!$E$5:$O$5,'Order Sheets'!$B$7+1,'Bakery Orders'!$E$68:$O$68)

since row 68 on Bakery Order sheet is for Rye Bread.

Make sense?

Did the print macro work as planned?

Cheers

Rich

Answer 2

OK, I think I've got it.  Try this:

'=======================================================

Sub PrintSheetIfColumnContainsTomorrowsDate()

     Dim sht As Worksheet

     Dim rngDays As Range, c As Range, rngFind As Range, rngOrder As Range

     For Each sht In Worksheets

        Select Case sht.Name

            Case "Front Sheet", "customer list", "pie orders", "bakery orders", _                "bread cut list", "delivery run", "invoice list", "Worksheet"                'Do nothing

            Case Else 'All other sheets not listed above will be tested!                Set rngDays = sht.Range("E8,G8,I8,K8,M8,O8")    'Range where Tue-Mon dates appear.

                For Each c In rngDays                           'Loop thro each cell in rngDays

                    If c.Value = DateSerial(Year(Now), Month(Now), Day(Now) + 1) Then 'If date in cell = tomorrow then

                         'Create a range of cells beneath the day-cell in rows 9 to 45

                        Set rngFind = Range(c.Offset(1, 0), c.Offset(37, 0))

                        If WorksheetFunction.CountBlank(rngFind) < 37 Then

                                sht.PageSetup.PrintArea = "$A$1:$Q$51" 'Set print area ##(P OR Q????)##

                                sht.PrintOut 'Print the sheet

                                GoTo NextSheet 'Skip the rest of the days on current sheet

                        End If

                    End If

                Next c

        End Select

NextSheet:  'DO NOT INDENT THIS LINE

     Next sht

End Sub

'=======================================================

Please check the list of sheet names not to be tested/printed is correct, and the print area is correct (you have said A1:P51 and A1:Q51 in your previous posts...).

Note: in order for this to NOT print a customer sheet, all cells under each day in that sheet must be blank (completely empty) or contain "" (a NullString).  Any cells containing " " (a space), for example, will cause that sheet to be printed.

You can't e-mail me directly unfortunately, but if this doesn't work and you need to share your workbook, you can upload the file through one of the free file hosting sites such as:

Windows Live Skydrive: http://skydrive.live.com

MediaFire: http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

FileDropper: http://www.filedropper.com

RapidShare: http://www.rapidshare.com

Box.Net: http://www.box.net/files

then post a link to it here.

Please let me know if it works (or not!).

Cheers

Rich

Answer 3

HI rich007,

I am very new to vba,

What i have is every customer has a column for each day fo the week.

My neighbour places the date of the tuesday for the week and then on each of the customer pages it then has tues to monday columns that he enters their orders.

I want to be able to create a print run sheets button so he doesnt have to check of the invoices he takes with him he can use each customer run sheet instead.

i was hoping to have ine button that searches each range for example f9:f45 if f8 is dated for tomorrow.

If there is nothing in the range of f9:f45 for tomorrows date then do not print that customer,but if there is then print the whole page range A1:q45

I have 40 custoemr pages and about 6 other pages that i have done my working out on and also the front page. There  are also pages that the orders collate to so can be sent to the pie or bakery so they can get orders ready for distribution.

Answer 4

If list of dates is in column F, then use:

Sub PrintSheetIfColumnContainsTomorrowsDate()

    Dim sht As Worksheet, rng As Range

    For Each sht In Worksheets

        Set rng = sht.Columns("F:F").Find(What:=DateSerial(Year(Now), Month(Now), Day(Now) + 1), _

            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

            MatchCase:=False, SearchFormat:=False)

        If Not rng Is Nothing Then

            sht.PageSetup.PrintArea = "$A$1:$P$51"

            sht.PrintOut

        End If

    Next sht

End Sub

This will only print a sheet if tomorrow's date is found in column F.

(not tested).

Cheers

Rich

Answer 5

Didn't my response in http://answers.microsoft.com/en-us/office/forum/office_2003-excel/macro-to-print/e331f362-be1f-46d1-8771-236ec8c8e6c5 answer your question?

Or do you need something like this:

Sub PrintSheetsIfOrderDateIsTomorrow()

    Dim sht As Worksheet

    For Each sht In Worksheets

        If sht.Range("A1").Value = DateSerial(Year(Now), Month(Now), Day(Now) + 1) Then

            sht.PageSetup.PrintArea = "$A$1:$P$51"

            sht.PrintOut

        End If

    Next sht

End Sub

Does that do what you want? Or do you need to print all the sheets with orders in one go (to get page numbers on them)?

Note: I assumed the date for the order is in cell A1.  You said "column for tomorrow's date".  Can you explain what you mean by that?  Do you need to search for tomorrow's date in a particular column, and if found the print that sheet?

Cheers

Rich