Dynamic Printing Question

[EMEZINSP]

I have combed through google and my excel book for something i am trying to do but can't find how.

What i am looking to accomplish should be simple.

I have 10 page breaks on my one sheet. All ten pages on my sheet have reports with formulas and links to other cells. But there is a middle part in those pages where i enter my data and i do not need every page printed every time unless my data goes down that far.

So what i am looking to do is on my first page i have e.g.: Page 1 Of 10, am i able to set up something that will allow me to change that to 1 Of 1 and only print the first page. or 1 Of 4 then have it print four? I have the form templates down all ten pages so i haven't been able to find something that will print the full page and only the pages i want.

I thought i could maybe somehow could use the page number in the cell to reference how many rows down i may need.

I probably have another 40-60 hours to get my sheet to where i think i want it and this has been stumping me for some time. No i cannot do headers and footer for my reports with my data in the middle.

Thanks in advance.

 

[Yongle]

with print area set to include all 10 pages, you could try this
- amend input box defaults when you run the code

Sub PrintPages()
    Dim first As Integer, last As Integer
    first = InputBox("First page?", "Print from...", 1)
    last = InputBox("Last page?", "Print up to...", 1)
    ActiveSheet.PrintOut From:=first, To:=last
End Sub

 

[EMEZINSP]

Not quite i was looking for. I want it to work off a Cell value on the first page, instead of typing it into a cell on the report then using the input box.

[TABLE="width: 366"]
<tbody>[TR]
[TD="colspan: 5"]Report Page Number[/TD]
[TD]Page[/TD]
[TD]1[/TD]
[TD="colspan: 2"]of[/TD]
[TD="colspan: 2"]1(Cell Y10)[/TD]
[/TR]
</tbody>[/TABLE]
(Prints first page on only have the first page active for when i print)

[TABLE="width: 366"]
<tbody>[TR]
[TD="colspan: 5"]Report Page Number[/TD]
[TD]Page[/TD]
[TD]1[/TD]
[TD="colspan: 2"]of[/TD]
[TD="colspan: 2"]10
[/TD]
[/TR]
</tbody>[/TABLE]
(Prints all 10 pages or has all ten pages active)
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">1 (Print first page, or only have 1st page active</body>

 

[Yongle]

ok - hopefully someone else can give you what you want

 

[EMEZINSP]

I figured out what works for me. Cell Value(Y10) is For how many pages i want 1-10, then it only prints the pages i want. It only activates the cells i want, i set the page breaks and it just works!



Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("Y10"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "1": Rows("65:667").EntireRow.Hidden = True
Rows("1:64").EntireRow.Hidden = False
Case Is = "2": Rows("132:667").EntireRow.Hidden = True
Rows("1:131").EntireRow.Hidden = False
Case Is = "3": Rows("199:667").EntireRow.Hidden = True
Rows("1:198").EntireRow.Hidden = False
Case Is = "4": Rows("266:667").EntireRow.Hidden = True
Rows("1:265").EntireRow.Hidden = False
Case Is = "5": Rows("333:667").EntireRow.Hidden = True
Rows("1:332").EntireRow.Hidden = False
Case Is = "6": Rows("400:667").EntireRow.Hidden = True
Rows("1:399").EntireRow.Hidden = False
Case Is = "7": Rows("467:667").EntireRow.Hidden = True
Rows("1:466").EntireRow.Hidden = False
Case Is = "8": Rows("534:667").EntireRow.Hidden = True
Rows("1:533").EntireRow.Hidden = False
Case Is = "9": Rows("601:667").EntireRow.Hidden = True
Rows("1:600").EntireRow.Hidden = False
Case Is = "10": Rows("1:667").EntireRow.Hidden = False
Case Is = "0": Rows("1:667").EntireRow.Hidden = False



End Select
End If
End Sub

 

[Yongle]

Select Case is a good way to deal with all those variations
Thanks for posting your solution


If you prefer shorter code, here is a way to do that
(it does exactly what your code is doing without repeating EntireRow.Hidden = True \ False every time. Because the total range is consistent, it is possible to hide with a single line)

Dim VisRng As Range
'Hide ALL relevant rows
   Rows("1:667").EntireRow.Hidden = True
'use select Case to set VisRng to rows to be visible
   Select Case
        Case Is = "1" :   Set VisRng = Rows("1:64")
        Case Is = "2" :   Set VisRng = Rows("1:131")

        etc
   End Select
'unhide rows in VisRng
   VisRng.EntireRow.Hidden = False