Toughie: Conditional macro to print specific ranges

[Scottrc35]

Hi, Complicated problem, and first time poster so be nice

Basically I need a macro to print specific ranges of cells from one sheet, based off of a number given in another cell. The macro needs to be activated by a button click.



The following is a more detailed example/question of what I'm trying to do and what I think I need to figure out how to do to accomplish it.


What I am trying to do:

I am putting together a fairly comprehensive list of names/information for work.

On "Sheet1" I've made the list with a maximum capacity of 2000 names (more then I will ever need) with a current total names of 540, Including search functionality using some moderately extensive formulas I strong together.

Now this list, While the interface/search is good its not vary printer friendly, so on "sheet2" I made a printer friendly version.

I set up sheet two to look like pages laid out, one on top of the other with 50 names on each page for a total of 40 pages laid out on "Sheet2"

Example: of sheet 2

-Cell A1:H8 Is misc info, and the button I want the users to click in order to print.

-Cell B9:H65 Is Page one, It’s a white area with the list for easy printing,

-Row 66 is blank. (The space between page 1 and 2)

-Cell B67:H123 is page two, this continues down to page forty.

I fixed some formulas to return a number in Cell H6 of how many pages has names on them. My goal in doing that number is I want to find a way to fix it so that when a user click's on a button on screen it runs a code to print the number of pages listed in Cell H6. So that Every time a user needs to print it will only print the pages containing information and not all 40 pages. Also I don’t want it to print the space between the pages (ea Row 66) I've been doing things with excel involving formulas for about a year, but my experience with VBA is pretty well limited to the recording function of excel.

So with the explanation out of the way. Now for my question:

I think I have an idea how to accomplish what I want.

1. I need to figure out how to make excel print a range from a macro (Button Click) Specifically the page areas, like B9:H65(Page one) or B67:H123 (Page two) and so on and so forth.

2. I need to Figure out how to make a conditional macro that Based off the number in cell H6 Will Print off the specified ranges. For example if the number In Cell H6 is 5, Clicking the macro button will Print off Range 1,2,3,4,&5.

Lastly I have already named each Range I want to print as my individual pages, Print_area1,Print_area2,Print_area3, etc.

Thanks.

 

[SydneyGeek]

Hi Scott, welcome to the Board!

If I understand you correctly, you have a cell (H6) with a number. You want to print from page 1 to whatever number you have in H6... maybe something like this?

Sub PrintPages()

    Dim ws As Worksheet
    Dim iLast As Integer
    
    Set ws = Sheets("Sheet2")
    iLast = ws.Range("H6").Value

    If IsNull(iLast) Or iLast < 0 Then 
        MsgBox "Please enter a value between 1 and 40 in H6!"
        Exit Sub
    End If

    ws.PrintOut From:=1, To:=iLast
    
End Sub

Denis