Print if cell contains value

    Please see the attached sheet wherein would like to print certain page number if the cell contains a value


    For. Eg.
    In sheet1, D5 contains a number so it should print page 1 to 2 on sheet2
    In Sheet2, D6 contains a number so it should print page 3 to 4 on sheet2
    and so on
    D9 does not contain a number or is 0 so nothing should be printed
    D10 contains data so it should print page 6 to 8 on sheet2


    When printing it should also ask for a selection of printer only once at the start and not for each page to what it needs to print

    Re: Print if cell contains value


    I do not understand what you mean by:

    Quote

    In sheet1, D5 contains a number so it should print page 1 to 2 on sheet2
    In Sheet2, D6 contains a number so it should print page 3 to 4 on sheet2


    Can you attach a file that shows your desired result.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Print if cell contains value


    What I mean to say is in Sheet1 D5 contains value 56
    D6 contains 78 and so on and D9 does not contain any value.


    Now when print command button is pressed on sheet1


    Then if there is any value in D6 then is should print page number 1 and 2 given in sheet2


    Then D7 should print pages 3 and 4 in sheet2


    Since there is no value in D9 nothing should be printed.


    Before printing It should ask for printer selection also.


    Attaching the sample excel sheet.


    Hope am clear

    Re: Print if cell contains value


    Sorry but I still do not follow, Sheet 2 now has 5 cells with the value "Sheet 1" separated by different number of rows between each. There is no indication of where the other sheet names will appear or what should be printed in each case.


    I will leave this to somebody who does understand your requirements.


    As it is because you want a complete code from scratch rather than help with a particular issue with some thing that you have tried yourself you question might be better placed in the Hire Help Forum.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Print if cell contains value


    Please give me a days time and I will prepare the exact worksheet and upload my requirements.
    I have also prepared a code but it does not work the way I want, will upload the code also.

    Re: Print if cell contains value


    Please see the modified copy of what I exactly require.


    Sheet1 named Rent contains all the details of the party.
    Sheet2 named Rent bills contains all the bills made.


    Currently When I click command button print it prints only pages 1 and 2 from the sheet named Rent Bills, and not all what I require.


    Now what I exactly require is


    The print out should be taken only when Cells in F contains a value.


    Here F6 contains the value so it should print page 1 and 2
    Here F7 contains the value so it should print page 3 and 4
    Here F8 does not contains any value so it skip
    Here F9 contains the value so it should print page 7 and 8


    and so on


    Before printing a dialog box should appear asking to select the printer only once and not for all printouts.


    Hope I am now clear.

    Re: Print if cell contains value


    The problem is that you are using If.....ElseIf........ElseIf......End If, so when an If statement is true the code for that If runs and the If..ElseIf...EndIf statement is exited, either use a separate If......End If statement for each row in column F, or use this which will work however many cells in column F have a value(from F5 to the last entry in column F).

    Code
    Sub Button1_Click()
    Dim r As Range
    
    For Each r In Range("f5:f" & Cells(Rows.Count, 6).End(xlUp).Row)
        If r <> "" Then Sheets("Rent Bills").PrintOut from:=r.Row - 4, to:=r.Row - 3
    Next
    
End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Print if cell contains value


    Code
    Application.Dialogs(xlDialogPrint).Show


    I would disagree about separate If......End If statements being better.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Print if cell contains value


    Sir, thanks for the print dialog box.
    Although it asks for selection of printer there are 2 glitches


    1. It asks for selection of printer for each page to be printed, whereas I would like it to ask only once.


    2. It prints sheet named Rent rather than Sheet named Rent Bills.


    Sir as far as I am comfortable with If and End if is because it gives the output perfectly well as I require rather than using your code.


    Please see the attached file wherein your Code has been kept and the code with Endif and If.


    If there is any mistake please let me know.

    Re: Print if cell contains value


    Did you change the Option in the Print Dialogue from 'ActiveSheet' to 'Selection'?


    It works for me and I am only asked to select the printer once.


    Note your End.....If code will print all pages because you are using


    Code
    If Sheets("rent").Range("f5").Value <> 0 Then


    instead of


    Code
    If Sheets("rent").Range("f5").Value <> "" Then

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Re: Print if cell contains value


    Sir the option to select the Option in the Print Dialogue to 'Selection' is not active.


    and even though I changed the VB code to


    Code
    [/COLOR]=If Sheets("rent").Range("f5").Value <> "" Then


    it still asks to select printer for all pages that is to be printed.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login