• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Printing multiple worksheets from a Table of Contents

bobbyd98682

New Member
Hello all,


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

[pre]
Code:
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
 
Have you had a look at : http://chandoo.org/wp/2011/09/14/hui%E2%80%99s-excel-report-printer/
 
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
 
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
 
Back
Top