Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    check to print sheet based on cell value (Excel2k)

    Hi all. I have an expense report template with multiple sheets. I'm trying to write a macro to print all of the pages where there is a value in a particular cell (say, A100). The code I have here works, but it is very clunky. Basically, I wanted to tell Excel to print Page1 (the sheet name) plus all pages (Page2, Page3, etc) where there is a value in cell a100.

    Any thoughts would be appreciated...thanks in advance.

    klyjen

    Sub PrintPages()

    Sheets("Page1").Select
    ActiveWindow.SelectedSheets.PrintOut
    'because sheet 1 should always be printed

    ' I named the cell (a100) to [Print2] on page2,
    ' [print3] on page3, etc, but that is very very clunky...

    If [Print2] > 0 Then
    Sheets("Page2").Select
    Selection.PrintOut
    End If

    If [Print3] > 0 Then
    Sheets("Page3").Select
    Selection.PrintOut
    End If

    'et cetera for additional pages

    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check to print sheet based on cell value (Excel2k)

    Something like this might work:

    <pre>Public Sub PrintPages
    Dim oSheet as Worksheet
    For Each oSheet in Worksheets
    If oSheet.Name = "Page1" Or oSheet.Range("A100")<>"" then oSheet.PrintOut
    Next oSheet
    End Sub
    </pre>


    Not tested, just typed into the reply. There may be typos.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check to print sheet based on cell value (Excel2k)

    Oh good grief.

    That's what I get for trying to make it too complicated.

    Sheesh.

    Thanks! That does exactly what I wanted it to do (except I am just checking the .range rather than the .name). I ALWAYS forget about that For Each statement--I was trying to do an array and all this other stuff and it Just Wasn't Working. Grr.

    klyjen

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: check to print sheet based on cell value (Excel2k)

    Legare used the Name property because you said you always wanted to print Page1.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check to print sheet based on cell value (Excel2k)

    Oh. Yeah. I did say that. Actually, in the interim, the trainer (who's building the interface for this expense report) set page1 to always have a value in that cell. It has been a crazy day, but I should've mentioned that in my first response.

    klyjen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •