Printing multiple worksheets from a Table of Contents

[bobbyd98682]

Hello all,


I have found (and tweaked) a wonderful macro that creates a hyperlinked table of contents (TOC):

[pre]

Sub Create_TOC()
Dim wbBook As Workbook
Dim wsActive As Worksheet
Dim wsSheet As Worksheet
Dim lnRow As Long
Dim lnPages As Long
Dim lnCount As Long
Set wbBook = ActiveWorkbook
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'If the TOC sheet already exist delete it and add a new
'worksheet.
On Error Resume Next
With wbBook
.Worksheets("TOC").Delete
.Worksheets.Add Before:=.Worksheets(1)
End With
On Error GoTo 0
Set wsActive = wbBook.ActiveSheet
With wsActive
.Name = "TOC"
With .Range("A1:B1")
.Value = VBA.Array("Table of Contents", "Sheet # - # of Pages")
.Font.Bold = True
End With
End With
lnRow = 2
lnCount = 1
'Iterate through the worksheets in the workbook and create
'sheetnames, add hyperlink and count & write the running number
'of pages to be printed for each sheet on the TOC sheet.
For Each wsSheet In wbBook.Worksheets
If wsSheet.Name <> wsActive.Name Then
wsSheet.Activate
With wsActive
.Hyperlinks.Add .Cells(lnRow, 1), "", _
SubAddress:="'" & wsSheet.Name & "'!A1", _
TextToDisplay:=wsSheet.Name
lnPages = wsSheet.PageSetup.Pages().Count
.Cells(lnRow, 2).Value = "'" & lnCount & "-" & lnPages
End With
lnRow = lnRow + 1
lnCount = lnCount + 1
End If
Next wsSheet
wsActive.Activate
wsActive.Columns("A:B").EntireColumn.AutoFit
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
ActiveWindow.Zoom = 75
End Sub

[/pre]

What I need is a macro that, while I am on the TOC worksheet, after I select one or more hyperlinked worksheets, will print them.


Any takers?


Thank you,


Bob

 

[Hui]

Have you had a look at : http://chandoo.org/wp/2011/09/14/hui%E2%80%99s-excel-report-printer/

 

[bobbyd98682]

Thank you Hui.


I just downloaded the sample file and will be trying it out shortly. I'll get back to you in a bit to see if it is what I need.

Thank you again.


Bob

 

[bobbyd98682]

Hi Hui,


I followed the instructions and copied Print_Control to the front of one of my workbooks. I then pressed the magic 'Setup Print Control Named Formula' button but nothing happened.

I've got 143 worksheets in this particular workbook so I'm not sure if it will work.

Is there a way to send you this particular workbook to see if it is just me?


Thank you,


Bob

 

[Hui]

Bob


Click Excel Ninja to the left

Email is at bottom of the page