Printing a number of pages based on a cell value

**Speshul** · 01-19-2012, 03:20 PM

Hello excelforum...

I'm working on a project right now and need some help on printing. The sheet has a total of 800 preformatted pages, each with formulas to pull up the needed information. The problem is, on a typical day only about 350 need to be printed. I created 800 for future expandability, and that number can't be changed.

The layout is 20 pages tall, 40 pages wide. Each 'section' of 20 pages needs to print only the populated pages, for example...

The first section, (which is the first 20 page tall column) needs 13 pages printed. The second section needs only 5. I need help figuring out how to tell excel to print pages 1-13, then pages 21-25, and so on until all 40 sections have been printed. The numbers '13' and '5' are in cells at the top of the sheet outside the print area, I am using a =count formula to determine how many populated pages there are.

Thanks in advance!

**Speshul** · 01-20-2012, 10:11 AM

bump?

**Speshul** · 01-20-2012, 11:17 AM

To put in code terms, what I actually need the sheet to do is this: (i know my syntax is nowhere near right but I just wanted to make something readable to a programmer :p)

dim A as integer = cell("A1").value 'section 1 start page
dim B as integer = cell("B1").value 'section 1 end page
dim C as integer = cell("C1").value 'section 2 start page
dim D as integer = cell("D1").value 'section 2 end page
'.....
'80 variables total, a start and end page for all 40 sections

Execute "PRINT(2,A,B,1,,,,,,,,2,,,TRUE,,FALSE)"
Execute "PRINT(2,C,D,1,,,,,,,,2,,,TRUE,,FALSE)"

**Speshul** · 01-20-2012, 04:44 PM

bump....no reply...

**Leith Ross** · 01-20-2012, 07:58 PM

Hello Spehsul,

You can use the Printout method for the worksheet to print the pages you want. Here is an example. This assumes the worksheet to be printed is named "Sheet2" and the cells that hold the page numbers are on another worksheet which is the active sheet.

Sub PrintPages()

    Dim C As Long
    
        For C = 1 To 40 Step 2
            Worksheets("Sheet2").PrintOut From:=Cells(1, C), To:=Cells(1, C + 1)
        Next C
    
End Sub

**Speshul** · 01-21-2012, 10:34 AM

Thanks for the reply

I actually came to a similar, though longer solution myself last night.
I have my sections labeled alphabetically, AA, AB, AC, and so on all the way to BN (40 total)

sub GOGOGOGOGO

Dim AA1, AA2, AA3 As String
AA1 = Range("C2").text  'This is my cell for Start Page
AA2 = Range("E2").text  'This is my cell for End Page
AA3 = Range("AE3").text 'This is my cell for total pages, which is a count formula

If AA3 > 0 Then Sheets("mysheet").PrintOut from:=AA1, to:=AA2, copies:=3 'only print section AA if there is at least 1 populated pahe

end sub

My way will take a lot of typing though, I will try yours before getting too far in mine :D