VBA print selected number of sheets

Morty

New Member
Joined
Jun 9, 2021
Messages
26
Hello guys, I have recently solved 1 problem here and I have another :D. Is it possible to make task manager in macro, which would ask which specific sheets to print? I have 10 sheets and there will be certainly times when I will use only for example first 3 of them. So to eliminate manual editing of macro (inserting which sheets to print by name) or to avoid unnecessary printing of all unused sheets. I would like to have pop up window, to which i would enter number of needed sheets. For example, the easiest way I guess would be set up the position number of last wanted sheet ( 3 =print first 3 sheets).

Here is the code which is working, but it prints all sheets except the first one which is used as summary.

VBA Code:
Sub print()
'UpdatebyExtendoffice20161031
 Dim xWs As Worksheet
 Application.ScreenUpdating = False
 For Each xWs In ThisWorkbook.Worksheets
  If xWs.Visible = True Then
   If xWs.Name <> "Summary" Then
       With xWs.PageSetup
        .PrintArea = "A1:K48" ' USE YOUR PRINTAREA
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
       End With
       xWs.PrintOut
   End If
  End If
 Next xWs
 Application.ScreenUpdating = False
 MsgBox "Sheet “Summary” has not been printed", vbInformation, "Kutools for Excel"
End Sub

I know there will be needed link between something like :

On Error Resume Next
resp = Application.InputBox(Prompt:="Insert number of desired sheets to print:", _
Title:="Total number of printed sheets", Type:=1) used from my another found out macro :D

number of Printout = resp ........or something like that, I am noob in VBA. I just have some basics in R software :D.

I sincerely thank you all for your time and aswers and I wish you a wonderful rest of the day.

Morty
 

Morty

New Member
Joined
Jun 9, 2021
Messages
26
Hello and thank you all :), yeah the @Logit code in post #6 is probably the easiest way :D. Just question how to add to that code desired range = "A1:K48" and portrait orientation. I am trying it around the ActiveWindow comand, but getting only errors :D
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,318
VBA Code:
Option Explicit

Sub SelectPrintArea()
Dim PrintThis As Range
ActiveSheet.PageSetup.PrintArea = ""
Set PrintThis = Application.InputBox _
  (Prompt:="Select the Print Range", Title:="Select", Type:=8)
 
    ActiveSheet.PageSetup.Orientation = xlLandscape
    PrintThis.Select
    Selection.Name = "NewPrint"
    ActiveSheet.PageSetup.PrintArea = "NewPrint"
    ActiveSheet.PrintPreview
    
End Sub

After initiating the macro, select the range to be printed using your mouse. Then click OK.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,318
My error. For some reason my mind was interpreting "landscape".

VBA Code:
Option Explicit

Sub SelectPrintArea()
Dim PrintThis As Range
ActiveSheet.PageSetup.PrintArea = ""
Set PrintThis = Application.InputBox _
  (Prompt:="Select the Print Range", Title:="Select", Type:=8)
 
    ActiveSheet.PageSetup.Orientation = xlPortrait
    PrintThis.Select
    Selection.Name = "NewPrint"
    ActiveSheet.PageSetup.PrintArea = "NewPrint"
    ActiveSheet.PrintPreview
    
End Sub
 

Morty

New Member
Joined
Jun 9, 2021
Messages
26
Thank you Logit, that´s nice macro, but not exactly what I would like :D. I have first sheet as "Summary" and after that 10 identical sheets where I will just edit locked cells and they are ready to print. So for these sheets I have estimated range of print area which is A1:K48. I just probably need to edit my macro which i inserted here to print only active sheets (manualy selected) or have InputBox to which i would set range of sheets to print :D. Now it sadly print all except the first 1 Summary sheet. Once again thank you both for your help :) and have a nice rest of day.

Morty
 

Morty

New Member
Joined
Jun 9, 2021
Messages
26
While continuing my search, I came across this macro :D. Which does what I have desired just dont know how :D. But it is printing selected sheets two-sided :/. Would need just 1 sheet printed per paper :D. But i would be happy even by printing only manualy choosen sheets by mouse, see my coment above this.

VBA Code:
Private Sub CommandButton1_Click()
'   Display "Printer Setup" dialog box
    Application.Dialogs(xlDialogPrinterSetup).Show
'   Option Explicit
'   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
    Dim Numcop As Long
    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
    x = CurrentSheet.Name
    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
'   Get the number of print copies for each report
    Numcop = Application.InputBox("Enter number of copies to print:", _
    "How Many Copies?", 1, Type:=1)
    If Numcop = 0 Then
    ElseIf Len(Numcop) > 0 Then
        End If
'   Display the dialog box
    CurrentSheet.Activate
    Dim cnt As Integer
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                    If cnt = 0 Then
                    Worksheets(cb.Caption).Select ' Replace:=False 'Activate
                Else
                    Worksheets(cb.Caption).Select Replace:=False 'Activate
                End If
                    cnt = cnt + 1
                End If
                Next cb
                    ActiveWindow.SelectedSheets.PrintOut copies:=Numcop
                    'ActiveSheet.PrintPreview 'for debugging
        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
    Sheets(x).Select
End Sub
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,836
Messages
5,766,713
Members
425,373
Latest member
ndiejennrrd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top