Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Printing Multiple sheets
I am trying to be able to print multiple #'s of sheet 1 and multiple #'s of sheet 2 and etc. I started out using this code for it but I can only print out 1 sheet at a time can anyone help me out if I need to print"so many" of sheet1 and a completely different amount of sheet2 Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets If Application.CountA(CurrentSheet.Cells) < 0 And _ CurrentSheet.Visible Then SheetCount = SheetCount + 1 PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 End If Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 .Caption = "Select sheets to print" End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True If SheetCount < 0 Then If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Activate ActiveSheet.PrintOut ' ActiveSheet.PrintPreview 'for debugging End If Next cb End If Else MsgBox "All worksheets are empty." End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub thanks, David -- light_life_love ------------------------------------------------------------------------ light_life_love's Profile: http://www.excelforum.com/member.php...o&userid=26647 View this thread: http://www.excelforum.com/showthread...hreadid=399228 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Multiple sheets on ONE? | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |