Macro to Print Specific # of Pages based on a cell's value

[liquidmettle]

Hi Everyone,

I am looking to develop a macro that will be tied to a button.

When a user presses the button, I want a specific number of pages to print based on a single cell's value.

Here are the particulars:

Sheet name: Reporting

Merged Cell that determines # of pages printed: EK25:EO26 (I don't know if the range the merged cell covers is important, but the top most left cell is EK25).

Possible Values for EK25: "Page 1", "Page 2",......, "Page 15". (Values in worksheet are without quotations).

I have set up, using page break view, a total of 15 pages (all vertically, no pages next to one another in the page break preview).

The total print range is from CO12 to DY481 (With the way the document is formatted, this is exactly 15 pages, normal 8 x 11.5 portrait orientation).

So what I want is a button that will print from page 1 to whatever value is listed in EK25.

If there is no data, EK25 will read "No Matching Criteria".

Is this function possible?

Thanks all in advance, you guys rock!

 

[mrmmickle1]

Maybe something like this would work:

[COLOR=#0000ff]Sub[/COLOR][COLOR=#000000] PrintToPageX[/COLOR]()

    [COLOR=#0000ff]Dim[/COLOR] PageTo [COLOR=#0000ff]As Integer[/COLOR]

    PageTo = Range("EK25").Value
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=[COLOR=#ff0000]PageTo[/COLOR], Copies:=1, Collate _
        :=[COLOR=#0000ff]True[/COLOR], IgnorePrintAreas:=[COLOR=#0000ff]False[/COLOR]
        
[COLOR=#0000ff]End Sub[/COLOR]

 

[liquidmettle]

Hi mrmmickle1,

This does indeed work! I had to change the way the page numbers were structured (I just split the cells so only the number went into EK25, not the word "page" and the number).

Additional Questions:

1. The report they will be printing has some color specific regions. While some people will just print it in black and white, is it possible to give them the option to change printer preferences? Or is this just something they need to do in advance? There is a guide associated with this dashboard so if its just something they need to do and cannot be added into the button's function then I will put a notice in there.

2. When there is no data entered, the field where the page number would appear says "No Matching Criteria". When you press the button under this condition, it gives a run-time debug error. Is it possible to not have that show up? If its not, once again no worries, I can just put a notice in the guide).

Thanks again!

-LM

 

[mrmmickle1]

liquidmettle,

This adjusted code will check if cell EK25 is blank("") and give an error msgbox:

[COLOR=#0000ff]Sub[/COLOR] PrintToPageX()

    [COLOR=#0000ff]Dim[/COLOR] PageTo [COLOR=#0000ff]As Integer[/COLOR]

    PageTo = Range("EK25").Value
    [COLOR=#0000ff]If [/COLOR]Range("EK25").Value = "" [COLOR=#0000ff]Then[/COLOR]
        MsgBox "Please Enter Page Number Into Cell EK25", vbCritical, "Print Criteria Not Entered"
[COLOR=#0000ff]        Exit Sub[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    ActiveWindow.SelectedSheets.PrintOut From:=1, To:=PageTo, Copies:=1, Collate _
        :=[COLOR=#0000ff]True[/COLOR], IgnorePrintAreas:=[COLOR=#0000ff]False[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

Not sure about the black and white setting. I don't have a color/black and white printer to test with. However, something like this may help:

Workbooks("Book1.xlsm").Worksheets("Sheet3").PageSetup.BlackAndWhite = [COLOR=#0000ff]True[/COLOR] [COLOR=#008000]'Change Accordingly[/COLOR]

 

[liquidmettle]

Hi again,

We can ignore the issue about which printer it goes to, I opted just to leave a notice.

I tried this code below:

Sub printreport()
Dim PageTo As Integer


PageTo = Range("Eg29").Value
If Range("Eg29").Value = "No Matching Criteria" Then
MsgBox "Please ensure the Measure Status selected to print has at least one measure present with that status", vbCritical, "Print Criteria Not Entered"
Exit Sub
End If
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=PageTo, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False

End Sub


I got the same run-time error "13" : Type Mismatch


So I imagine its not recognizing the "No Matching Criteria" text. Let me know if this is the case and how we might be able to resolve it.

I appreciate the help and time you're spending on my problem : ).

 

[mrmmickle1]

Change this line:

[COLOR=#0000ff]Dim[/COLOR][COLOR=#333333] PageTo [/COLOR][COLOR=#0000ff]As Intege[/COLOR][COLOR=#0000ff]r[/COLOR]

to

[COLOR=#0000ff]Dim[/COLOR][COLOR=#333333] PageTo [/COLOR][COLOR=#0000ff]As Variant[/COLOR]

The variable PageTo was defined as an Integer (a whole number) so if Range("EG29").Value is text it will result in an error.

Hope this helps

 

[liquidmettle]

Works perfect. You're awesome!

Thanks,

-LM

 

[mrmmickle1]

Your Welcome! Happy I could help!

 

[asad]

Hello mrmmickle,

I have something similar. How can I adjust the code so that it prints only the pages 1, 3, 5, and 7 in .pdf format. I don't even know if this is possible or not. Can you help please?

Asad

 

[asad]

I have tried this

Sub PrintToPageX()
    Dim rng As Range
    Set rng = Range("Z1:Z5")
   With rng
          
    ActiveWindow.SelectedSheets.PrintOut From:=rng, To:=rng, Copies:=1, PrintToFile:=Test1.pdf, Collate _
:=True, IgnorePrintAreas:=False
    End With
    
End Sub

But no luck