VBA solution for printing specific pages
 

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

VBA solution for printing specific pages
 

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" wrote:

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

VBA solution for printing specific pages
 

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

"Sam Wilson" wrote:

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" wrote:

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

VBA solution for printing specific pages
 

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

"Dickie Worton" wrote:

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

"Sam Wilson" wrote:

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" wrote:

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