Print a list of worksheets

[Chris Nichol]

I have a list of worksheets in the same work book to print every week, the
list changes every week (data comes from an acess query) and is on the first
sheet of the workbook. I would like to automate this with VBA so that it
will run the query then print all of the relevant worksheets.

Any help with this would be appreciated
Chris

[Nick Hodge]

Chris

Without knowing all the constrains you may have it's difficult to code, but
if the list is always the first sheet and it is the only data on the sheet.
the following, in limited testing works.

Sub printSheetsFromList()
Dim listRange As Range
Dim myCell As Range
On Error Resume Next
Set listRange = ThisWorkbook.Worksheets(1).UsedRange
For Each myCell In listRange
Worksheets(myCell.Value).PrintOut
Next myCell
End Sub

If it is not the only data on the sheet, you may need to take another route
than UsedRange, etc.

--
HTH

Nick Hodge
Microsoft MVP - [Excel]
Southampton, England

nick_...@lineone.net

"Chris Nichol" <ch...@chrisnichol.freeserve.co.uk> wrote in message
news:uuBnVun7$GA.247@cppssbbsa05...

[William]

Nick, (or anyone with potential solutions)

Thanks for the sample code. It's almost exactly what I need for my
application. However, there's one additional function I'd like to be able
to achieve. I'd like to be able to print all of the worksheets that are
contained in a list, in Column A, but only print the range that is entered
as a text value in column B. The range listed in column B will be different
for each worhsheet listed in column A.

Example:

ColumnA ColumnB
Sheet1 A1:B9
Sheet2 C8:D91
Sheet18 D17:K87

I've modified the routine you put together for Chris as follows:

Sub Print_financials()
'
' Print_financials Macro
' Macro recorded 7/21/2000 by William S. Reiser
'

Dim listRange As Range
Dim myCell As Range
On Error Resume Next

Set listRange = ThisWorkbook.Worksheets("File_Maint").Range("Stmt_reports")

For Each myCell In listRange

Worksheets(myCell.Value).Range("c3:d8").PrintOut
Next myCell

'
End Sub

This works well, but I don't know how to get it to let me have a different
print range for each of the worksheets that is printed. I'd like to get the
print range specified in the command:

Worksheets(myCell.Value).Range("c3:d8").PrintOut

To pull from the column to the right of the named Range "Stmt_Reports"
(actually column A). Does this make sense?
..Do you have any idea how I'd achieve this?

Thank you so much for your assistance

Will R.
will...@hotmail.com

Nick Hodge <nick_...@lineone.net> wrote in message
news:OuciEAo7$GA...@cppssbbsa02.microsoft.com...

[Nick Hodge]

William

The following should achieve it.

Sub Print_financials()

Dim listRange As Range
Dim myCell As Range

Dim printRange As String
On Error Resume Next
Set listRange = ThisWorkbook.Worksheets("File_Maint").Range("Stmt-reports")
With Application
.ScreenUpdating = False

For Each myCell In listRange

printRange = myCell.Offset(0, 1).Value
With Worksheets(myCell.Value)
.PageSetup.PrintArea = printRange
.PrintOut
End With
Next myCell
.ScreenUpdating = True
End With
End Sub

--
HTH

Nick Hodge
Microsoft MVP - [Excel]
Southampton, England

nick_...@lineone.net

"William" <will...@hotmail.com> wrote in message
news:mL7e5.5742$u56....@typhoon.columbus.rr.com...