Select Print Multiple Sheets

**trickard** · 09-25-2009, 11:27 AM

I have a program pretty big, with over 85 separate sheets, i created a page and macro's to view/print each individual form/sheet - what would be the best way to let the user choose a number of different forms/sheets and click print once. Please any suggestions would be great...

Thanks

Tony

this is not solved

**martindwilson** · 09-25-2009, 11:45 AM

depends , most printers will let you choose which pages to print

**trickard** · 09-25-2009, 12:00 PM

the page has a list of over 85 forms/sheets - presently the user clicks on the macro to each individual cell and that takes you to the form where they then print. Every user has different needs as to which forms to print, so i was hoping to create something that once clicked a box appears and they can choose and/or a check box and if they say check boxes 3,4,6,13,15,28,and 77 those would be the only forms to print .......

**martindwilson** · 09-25-2009, 09:35 PM

yeah i thought about that after i posted. def possible with some code but thats a tad beyond me.
probably use a check box or similar then print all sheets that = true
luckily there are some instructions here but 85 check boxes/sheets will take a bit of time to do
http://www.mrexcel.com/forum/showthread.php?t=2925
attached is one i did from the instructions from baby tiger in link.
(sheets martin and fred) i left sheet 1 and 4 as they are so you can see what was done
(note to change check box names right click on check box in user form and change caption)
mind you once its done think how good you'll feel lol

**trickard** · 09-25-2009, 10:09 PM

thanks for the reply - i have seen/tried that before - the problem is this -

1. sheet has the list of over 85 forms/sheets
2. those sheets are all hidden
3. i have macros on each that opens/view the specific form/sheet & hides the main form - then a macro on each form/page that unhides main sheet and hides the form/sheet that was opened
4. i thought if i place a checkbox next to each title then the user can select the forms to print as a group - the one you did would/could work but it does a debug error i believe so those sheets are hidden so any ideas?

**martindwilson** · 09-25-2009, 10:24 PM

you could probably unhide and the re hide all the sheets except first one when macro is run

**trickard** · 09-25-2009, 10:42 PM

you know i messed around with it and it appears do work on the sample if for example martin, fred are hidden it opens and prints so this weekend i will see if it works see below the code

Private Sub CommandButton1_Click()
If CheckBox1.Value = True Then
Sheets("martin").Visible = True
ActiveWindow.SmallScroll Down:=-23
Sheets("martin").PrintOut Copies:=1
Sheets("martin").Select
ActiveWindow.SelectedSheets.Visible = False

End If
If CheckBox2.Value = True Then
Sheets("fred").Visible = True
ActiveWindow.SmallScroll Down:=-23
Sheets("fred").PrintOut Copies:=1
Sheets("fred").Select
ActiveWindow.SelectedSheets.Visible = False

End If

If CheckBox3.Value = True Then Sheets("Sheet3").PrintOut Copies:=1
If CheckBox4.Value = True Then Sheets("Sheet4").PrintOut Copies:=1
Unload UserForm1

End Sub

**martindwilson** · 09-25-2009, 11:12 PM

Code:

Private Sub CommandButton1_Click()
Dim wssheet As Worksheet

Application.ScreenUpdating = False
For Each wssheet In ActiveWorkbook.Worksheets
If Not wssheet.Name = "front sheet" Then wssheet.Visible = xlSheetVisible
Next wssheet
If CheckBox1.Value = True Then Sheets("martin").PrintOut Copies:=1
If CheckBox2.Value = True Then Sheets("fred").PrintOut Copies:=1
If CheckBox3.Value = True Then Sheets("Sheet3").PrintOut Copies:=1
If CheckBox4.Value = True Then Sheets("Sheet4").PrintOut Copies:=1
Unload UserForm1
For Each wssheet In ActiveWorkbook.Worksheets
If Not wssheet.Name = "front sheet" Then wssheet.Visible = xlSheetHidden
Next wssheet
Application.ScreenUpdating = True
End Sub

note for some reason it fails if sheet1 is used as the first sheet but using a name like front sheet works