VBA solution for printing specific pages

[Dickie Worton]

Can anyone help me with the following, please?

I have an Excel workbook in which the first page is a summary or index
sheet, containing hyperlinks to each of the subsequent sheets.

I'd like to be able to allow those to whom I am distributing it the option
of printing a predetermined selection of those sheets, based on the whether
or not a specific cell in those sheets contains a specified value.

For example, sheets no's 1, 4 & 6 all contain a specific value in cell L6;
I'd like users to be able to click on a macro on the summary page and for the
macro to locate and print a copy of sheet no's 1, 4 & 6 before returning the
user to the summary page. The values in cell L6 may change, although they
should never deviate from a range of options chosen from a drop-down list.

As such, what I am after is a macro or some VBA code that will search for
each page where that value occurs in cell L6 and prints that page, rather
than a more simple macro that directs users to a pre-defined list of pages,
seeing as these may change.

Is this possible at all? I'm trying to build some future-proofing of sorts
into this workbook so that the macros or code don't need rewriting each time
there is a variation to the value in cell L6 on any of the sheets.

Any suggestions greatly appreciated.

Thanks,

Dickie

 

[Sam Wilson]

Hi,

Sub demo()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("L6").Value = "WhateverYouWant" Then
ws.PrintOut
End If
Next ws

End Sub

Should do it, or at least give you the right idea

 

[Dickie Worton]

Hi Sam,

Looks simple enough, I'm a fairly limited VBA programmer but will give it a
go and let you know how I get on.

Thanks for the speedy reply, very much appreciated.

Dickie

 

[Dickie Worton]

Sam,

Thanks again, I have tried this out and it works like a charm. I've been
able to adapt it to cover several different options, particularly useful as
there are over 50 sheets in the workbook and the last thing I wanted was for
people to simply print them all to get the 10 or so they needed.

Much obliged to you!

Dickie