98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Printing a number of tabs from workbook
Printing a number of tabs from workbook
Resolved · High Priority · Version 2007
Tue 14 Aug 2012, 16:20Delegate Shirin said...
Shirin has attended:
Excel VBA Intro Intermediate course
Printing a number of tabs from workbook
Hi
I am trying to create a macro that would preview and print a number of sheets from a workbook. The trouble is, if someone adds a tab then the macro falls over.
Any suggestions?
Below is the current macro:
'
' Print_file Macro
'
'
Sheets(Array("Title page", "Table of Contents", "Current Year", "CY-Dir Issue", _
"CY - Causal", "CY - Resourcing", "CY - Risks & Ops", "Future Year", "FY-Dir Issue" _
, "FY - Causal", "FY - Resourcing", "FY - Risks & Ops")).PrintPreview
Sheets("Title page").Select
End Sub
Mon 20 Aug 2012, 13:35Trainer Simon said...
RE: Printing a number of tabs from workbook
Hi Shirin,
Thank you for your question.
Here is some code that will print preview each sheet and then do the printing. It is a little loop that refers to the Collection of sheets. As we are using the Collection, it means as you add more tabs they are automtically added to the Collection so the macro won't fall over.
------------------------------------------------------------------
Sub Print_file2()
Dim wsSheet As Worksheet ' worksheet variable
For Each wsSheet In Sheets ' For next loop that loops through
' each sheet in the collection
wsSheet.PrintPreview
wsSheet.PrintOut
Next wsSheet
End Sub
------------------------------------------------------------------
I hope this helps and answers your question.
Regards
Simon
Tue 21 Aug 2012, 10:09Delegate Shirin said...
RE: Printing a number of tabs from workbook
Hi Simon,
Thanks for that. Unfortunately not what I need as I need the sheets to be printed as a part of a pack with correct page numbers on them etc. This macro seems to treat each tab separately and somehow changes every page number to 1 as well!
I have simplified my previous macro to the following:
Public Sub Print_pack_v5()
'
' Print_file Macro
'
'
Sheets.PrintPreview
ThisWorkbook.Sheets.Select
End Sub
This selects the sheets and prints them. However, when I add new sheets it fails to see those as part of the pack to be printed.
Any ideas?
Shirin
Thu 13 Dec 2012, 11:11Trainer Cindy said...
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Thu 20 Dec 2012: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Hide separate columns in Excel 2010If you want to hide columns not adjacent to each other for example, Columns A, C and E then:- |
