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

Simple VBA Request

Montrey

Montrey

Member
I need some VBA code to attach to a button.

I need the code to select 3 sheets and bring up the print dialog box(Where you can choose printer and such)

The 3 sheet names are 'SMY' 'Flash' 'Summary'

Then obviously deselect the sheets after it prints.


Thanks guys!
 
How's this?

[pre]
Code: 
Sub PrintSheets()
Dim StartSheet As String
StartSheet = ActiveSheet.Name
'Select sheets
Sheets(Array("SMY", "Flash", "Summary")).Select

'Show dialogue
Application.Dialogs(xlDialogPrint).Show

'Go back to where we started
Sheets(StartSheet).Select

End Sub
[/pre]
 
You would need to set that using the Page Setup for that sheet, before the macro is run. Good news is, it's a one time thing. Each worksheet has it's own page setup settings.
 
The page setup for the flash sheet is set to legal.

The page setup for the other 2 sheets are set to letter.

When I use the macro they all print to letter size
 
Good evening Luke M


Just starting to dig in to the murky world of VBA and I am trying to read VBA posts to understand the code......But as I do this I end up with questions rolling around an empty head. Question, how would you alter the code to ask which sheets in a workbook to print if you did not have a specific named number as Montrey has
 
@Montrey

How about this for a workaround?

[pre]
Code: 
Sub PrintSheets()
Dim StartSheet As String
StartSheet = ActiveSheet.Name
'Select sheets
Sheets(Array("SMY", "Summary")).Select

'Show dialogue. Get's printer chosen and prints first 2 sheets
Application.Dialogs(xlDialogPrint).Show

Sheets("Flash").Select
ActiveSheet.PageSetup.PaperSize = xlPaperLegal
ActiveSheet.PrintOut 'Print the remainder

'Go back to where we started
Sheets(StartSheet).Select

End Sub
@bobhc

You could either build an array using a range of cells (check out the Join method) or you could use some InputBoxes to get the info from the user. Maybe something like:

x = InputBox("What is first sheet?")
y = InputBox("What is second sheet?")
Z = InputBox("What is third sheet?")

Sheets(Array(x, y, Z)).Select
[/pre]
 
Hi, Montrey!

Gave a look to this Hui's post? It deserves doing so.

http://chandoo.org/forums/topic/print-macro-for-selected-sheets-from-200-worksheets

Regards!
 
You must log in or register to reply here.
Back
Top