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!

 

[shg]

How about just hiding the rows you don't want printed?

 

[asad]

The following code works a bit but not what I want

Sub PrintToPageX()
    Dim rng As Range
   Set rng = Range("Z1:Z5")
          
    ActiveWindow.SelectedSheets.PrintOut From:=rng, To:=rng
    
End Sub

It prints all pages as separate files. I want it as one file.

 

[asad]

How about just hiding the rows you don't want printed?

I have tables and charts on pages 3, 5, and 7. If I hide the rows not wanting to print, the chart does not pick the values and shows empty with #REF!

 

[shg]

Select the chart, click Select Data, click Hidden and Empty Cells, tick Show data in hidden rows and columns

 

[asad]

Select the chart, click Select Data, click Hidden and Empty Cells, tick Show data in hidden rows and columns

Perfect. Thanks a lot shg.

Asad

 

[shg]

You're welcome.

 

[mrmmickle1]

Asad,

Sorry for not replying. I'm glad shg was able to get your issue sorted out!