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
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
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
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.
