VBA print selected number of sheets

Morty

New Member
Joined
Jun 9, 2021
Messages
25
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Add in your code to ask the user for the number of sheets to print and then
use the From & To in your xWs.Printout line of code

Example if you don't want to printout the 1st sheet(Summary): xWs.PrintOut From:=2, To:=resp
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
This is one option :

VBA Code:
Option Explicit

Sub commandbutton1_click()
    Application.Dialogs(xlDialogPrint).Show
End Sub


And here is a much more involved option :

Code:
Option Explicit

Private Sub PrintButton1_Click()
Dim i As Integer
Dim TotalPages As Integer
Dim CurrentPage As Integer
Dim CheckBox3 As Object
'Close userform
UserForm1.Hide

'Count Pages
TotalPages = 0
CurrentPage = 1

With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then TotalPages = TotalPages + 1
  Next i
End With


With ListBox1
  For i = 0 To .ListCount - 1
    If .Selected(i) Then
      Worksheets(.List(i)).Activate
          
      If CheckBox1 Then
        ActiveSheet.PrintPreview 'for debugging
      Else
        ActiveSheet.PrintOut 'Printout
      End If
    End If
  Next i
 
End With
Sheets("Sheet1").Select
End Sub

Private Sub CommandButton2_Click()
  UserForm1.Hide
End Sub

Sub UserForm_Activate()

Dim i As Integer
Dim PL As String, PS As String
Dim SheetCount As Integer
Dim CurrentSheet As Worksheet
Application.ScreenUpdating = False

'Setup Userform
ListBox1.Clear
CheckBox1.Value = False
Label2.Caption = Application.ActivePrinter

' 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

SheetCount = 0

Dim strArray() As Variant
ReDim strArray(1 To ActiveWorkbook.Worksheets.Count, 1 To 3)

' Add the items to the listbox
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
    strArray(i, 1) = CurrentSheet.Name
    
    Select Case CurrentSheet.PageSetup.PaperSize
    Case 1
      PS = "Letter"
    Case 2
      PS = "Letter Small"
    Case 3
      PS = "Tabloid"
    Case 4
      PS = "Ledger"
    Case 5
      PS = "Legal"
    Case 6
      PS = "Statement"
    Case 8
      PS = "A3"
    Case 9
      PS = "A4"
    Case 11
      PS = "A5"
    Case Else
      PS = "A4"
      CurrentSheet.PageSetup.PaperSize = xlPaperA4
    End Select
    strArray(i, 2) = PS
    
    'PL = "Landscape"
    CurrentSheet.PageSetup.Orientation = xlLandscape
  
    'If CurrentSheet.PageSetup.Orientation = 1 Then
    '  PL = "Landscape"
    'Else
    '  PL = "Landscape"
    'End If
    'strArray(i, 3) = PL
    
  End If
Next i

'Load the Listbox
ListBox1.List = strArray

End Sub

Private Sub CheckBox2_Click()
  Call Toggle_Selections
End Sub

Function Toggle_Selections()
Dim i As Integer

'Reverse all Listbox Selections
With ListBox1
  For i = 0 To .ListCount - 1
    .Selected(i) = Not (.Selected(i))
  Next
End With
End Function

Download example workbook : Print_Using_ListBox3.xlsm
 

Morty

New Member
Joined
Jun 9, 2021
Messages
25
Thank you for your replies :), i worry your solution is too complex for me Logit. I tried it but it seems its too complex for me and my coworkers :D. It is printing desired sheets but they are width oriented.

For johnnyL: I tried to incorporate your advice to my code :

VBA Code:
Sub Tisk()

'UpdatebyExtendoffice20161031
 Dim xWs As Worksheet
 Application.ScreenUpdating = False
 
[B] resp = Application.InputBox(Prompt:="Insert number of desired sheets to print:", _
 Title:="Total number of printed sheets", Type:=1)[/B]
 
 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
       [B]xWs.PrintOut From:=2, To:=resp[/B]
   
End If
  End If
 Next xWs
 Application.ScreenUpdating = False
 
End Sub

But sadly it just displays the pop up window per print for all sheets separately, but physically nothing comes out from printer :D. So it doesnt print chosen sheets :D.
 

Morty

New Member
Joined
Jun 9, 2021
Messages
25

ADVERTISEMENT

And just from curiosity, is it possible after setting the number of desired sheets to print them separatly as PDF to destinated folder ? :D
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
Ok ... this is as simple as it gets. The user will click on the tab / s of the sheet / s they want to print. When clicking on multiple tabs,
press and hold the CTRL key.

VBA Code:
Option Explicit

Sub PrntSelShts()
'Print all selected sheets
    ActiveWindow.SelectedSheets.PrintOut
End Sub
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Ahhh, I think I misunderstood your intentions @Morty. I was thinking you were referring to page numbers to print out. After rereading the thread I think you are referring to worksheets to print out, is that correct? you are referring to worksheets to print out?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Thank you for your replies :), i worry your solution is too complex for me Logit. I tried it but it seems its too complex for me and my coworkers :D. It is printing desired sheets but they are width oriented.
That is because @Logit code was set up to print 'Landscape'

You could change:

VBA Code:
    'PL = "Landscape"
    CurrentSheet.PageSetup.Orientation = xlLandscape

to:

VBA Code:
    'Portrait
    CurrentSheet.PageSetup.Orientation = xlPortrait
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,295
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
And just from curiosity, is it possible after setting the number of desired sheets to print them separatly as PDF to destinated folder ? :D
I would assume that deserves a separate thread because it differs from your original question.
 

Forum statistics

Threads
1,140,997
Messages
5,703,633
Members
421,307
Latest member
morrden86

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