Printing multiple sheets in a workbook with hundreds of sheets. From a list on a specific sheet.

Javi · Feb 18, 2014

Hi All,

I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.

The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.

Any help would be greatly appreciated

sijpie · Feb 21, 2014

OK, you will need to do some work here. It is not difficult if you follow the instructions exactly. What we are going to do is create a userform where you can select the sheet names and which will handle what to do when you press the OK button.

Open your workbook (with all the sheets). Close any other workbooks to keep things easy.
Now press Alt-F11 to open the VBA editor. You will see in the top left panel your workbook with all the sheets listed.
Somewhere there right click your mouse and select 'Insert...' / 'User Form'
In the right panel an empty userform appears.
Hopefully in the bottom left panel a list of properties for the form is shown. If not press F4.
The very first item of the properties is '(Name)' which reads UserForm1
This is pretty meaningless, so rename it to 'SheetsForPrinting'
Go down the list in the properties to 'Caption'. It says 'UserForm1'.
Change the caption to "Sheets for Printing"
Look at your form, you can see the new title it has.
Click once on the form. Hopefully a little toolbox window will appear. if not goto 'View' in the menu and select 'Toolbox'
You want to know what the form does, so let's put some text on it. click on the 'A' in the toolbox to paint a label.
Take your mouse to the userform and from top left to about a cm down and to the right of the form 'paint' a label box
put the cursor in this box and type (or copy /paste from here):"Select the sheets you want to have printed form the list. Use Ctrl key to select multiple individual sheets"
OK, now we need to be able to do something useful with the form. Go to the Toolbox and hover your mouse over the icons till you find 'ListBox' (normally 1st icon on second row). Click on it
Now back to the userform and paint a square below your label to just above the bottom of the form. This is where the sheet names will appear (when called)
Lastly we need some buttons: on the Toolbox find the little button icon (CommandButton). Click it
Back to the form and to the right of our square paint a button
click once on the button. Then hold down the Ctrl key and drag a copy of the button to below the first button (handy way to keep buttons same size)
In the Properties window (bottom right) change the caption of the button to 'Cancel'
Click once on the first button
In the Properties window change the caption of the button to 'OK'
Go a bit further down the properties list to 'Default'. set that to True (this means it will react to the Enter key)

Hey you now have a wonderful form! Should look something like this:

Click on your userform title and press F5. Hey there it is. But the list is empty and the buttons don't do anything.
Press the red close button to close the form.
Now let's start with the cancel button. Double click on the cancel button. The form disappears and an empty white space is looking at you with a skeleton macro in it for the cancel button.
Delete everything and paste the following text:

Option Explicit Private Sub CommandButton2_Click() ' Cancel Button Unload Me End Sub

Basically it says: if you click the cancel button then unload (close) the userform (Me)
You can try it. Press F5. the cancel button should now work
Now paste the following code below the code of the cancel button

Private Sub CommandButton1_Click() ' OK button Dim i As Long, j As Long Dim vShList As Variant With ListBox1 ' use an array to store the selected sheets ReDim vShList(1 To .ListCount, 1 To 1) j = 1 For i = 0 To .ListCount - 1 If .Selected(i) Then vShList(j, 1) = .List(i) j = j + 1 End If Next i If j > 1 Then 'at least one sheet was selected 'clear current sheet list and add new list With Sheets("Main_List").Range("D2") .Resize(.End(xlDown).Row - 1, 1).ClearContents ' add new list to sheet .Resize(j - 1, 1).Value = vShList End With End If Unload Me End With End Sub Private Sub UserForm_Initialize() Dim wsWS As Worksheet ' fill the list box with all sheet names, but not Main_List For Each wsWS In ThisWorkbook.Worksheets If wsWS.Name <> "Main_List" Then ListBox1.AddItem (wsWS.Name) End If Next wsWS End Sub 

OK now we have added the code for the OK button and for loading (starting) the userform.
Press F5 again. Wow! you can see all the sheets in the listbox. Select a few. Now press the OK button. Go to Main_List. The selected names should be in column D.

OK but we need one more thing. We need to call up the user form. So we write a little macro and attach that to a button which we stick in Main_List.

Go back to the VBA editor. Now right click again on the workbook in the top left panel and select 'Insert..' / 'Module'
A new section Modules will appear below the workbook and the right panel has cleared with the new module open.
Here we can write macros that we can access from the workbook.
Paste the following macro:

Option Explicit Sub SelectSheets() ' call userform to select sheets for printing SheetsforPrinting.Show End Sub </FONT

Now if you want to run this using a shortcut key, go to Excel. Press Alt-F8. It will show a dialog box with your macro 'SelectSheets' shown.
Select the macro (don't double click)
Press the Option... button and select a suitable shortcut sheet for it (preferably something with Shift, as a lot of the other keys already do something)
Click OK and then go back to the sheet. In cell D1 or so type your shortcut in (Ctrl-Shift-S for instance) so you don't forget
Then try it.
Finished

If you rather have a button, then you probably first need to enable the 'Developers' menu.
Right click on the menu ribbon, select 'Customise the Ribbon...'
In the right panel check the tickbox infront of 'Developer'.Click OK
Go to the Developer menu
Press the 'Insert' icon
select the button from the 'Form Controls' (top left icon)
in your spreadsheet paint your button
A dialog box opens to ask which macro it should run. Select your macro, click OK
The button appears, Click on the button to edit the text to somethin meaning full (right clicking allows you to change size, position, text)
Press the button

Let me know if this worked and how happy you now are!

Javi · Feb 21, 2014

Worked excellent!! thank you I appreciate your time...

Now I do have a different code I would like to use for printiing but I can't seem to make it work properly.

I'm trying to have it coded red allow me to select the column that it uses for printing rather than have it hardcoded in the code.

As I understand it this is a statement that indicates what column contains the sheet names that will determine what sheets to print (Const cl& = 1) ... I would like to have the code in red enter the value here representing what column to use.

The code works fine but I need to add the flexibility of the user selecting the column with a sheet names for printing.

Code:

Sub Add_Sheets_From_Col()Range("XA1") = ActiveSheet.NameActiveSheet.Name = ("Add_Sheets")   [COLOR=#ff0000]Dim myRangecol As Range     Set myRangecol = Application.InputBox(Prompt:= _        "Please Select a Column example for column A = A:A", _        Title:="InputBox Method", Type:=8)     If myRangecol Is Nothing Then        ' Range is blank    Else        myRangecol.Select[/COLOR]        [COLOR=#ff0000]   End If[/COLOR][COLOR=#800080]Const cl& = 1[/COLOR][COLOR=#00ff00] [/COLOR]Dim a As Variant, x As Worksheet, sh As WorksheetDim rws&, cls&, p&, I&, rr&, b As BooleanApplication.ScreenUpdating = False'ActiveSheet.ActivateSheets("Add_Sheets").Activaterws = Cells.Find("*", , , , xlByRows, xlPrevious).Rowcls = Cells.Find("*", , , , xlByColumns, xlPrevious).ColumnSet x = Sheets.Add(after:=Sheets("Add_Sheets"))'Set x = Sheets.AddSheets("Add_Sheets").Cells(1).Resize(rws, cls).Copy x.Cells(1)'ActiveSheet.Cells(1).Resize(rws, cls).Copy x.Cells(1)Set a = x.Cells(1).Resize(rws, cls)a.Sort a(1, cl), 2, Header:=xlYesa = a.Resize(rws + 1)    p = 2    For I = p To rws + 1        If a(I, cl) <> a(p, cl) Then            b = False            For Each sh In Worksheets                If sh.Name = a(p, cl) Then b = True: Exit For            Next            If Not b Then 'Sheets.Add.Name = a(p, cl)                Sheets.Add.Name = a(p, cl)                With Sheets(a(p, cl))                    x.Cells(1).Resize(, cls).Copy .Cells(1)                    rr = .Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1                    x.Cells(p, 1).Resize(I - p, cls).Cut .Cells(rr, 1)                End With            End If            p = I        End If    Next IApplication.DisplayAlerts = False    x.DeleteApplication.DisplayAlerts = TrueApplication.ScreenUpdating = TrueSheets("Add_Sheets").SelectActiveSheet.Name = [XA1]Range("XA1").ClearContentsRange("A1").Select'Sheets("Bin_Qty_Off_Line").Activate'Range("G1").SelectEnd Sub</PRE>

Javi · Feb 21, 2014

Sorry for the above reply trying to multitask and it's not working out.

Worked excellent!! thank you I appreciate your time...

Now I do have a different code I would like to use for printiing but I can't seem to make it work properly.

I'm trying to have it coded red allow me to select the column that it uses for printing rather than have it hardcoded in the code.

The code (Prt_All_From_List) in black works fine for printing all sheets as long as the sheet names are in column "D". However, I would like to combine the code in red as it works well for selecting a column I have used in the past,but I can't seem to work out how to combine these two codes.

I apologize for the previous response and the confusion.

Code:

[COLOR=#ff0000]

Sub ask_For_Location

Dim myRangecol As Range[/COLOR]
[COLOR=#ff0000]  Set myRangecol = Application.InputBox(Prompt:= _
      "Please Select a Column example for column A = A:A", _
       Title:="InputBox Method", Type:=8)[/COLOR]
[COLOR=#ff0000]   If myRangecol Is Nothing Then
      '   Range is blank
   Else
     myRangecol.Select
   
    End If

[/COLOR]

Sub Prt_All_From_List()
Dim response As VbMsgBoxResult, ShName As String, LR As Long, I As Long
response = MsgBox("Do you want to print all sheets", vbQuestion + vbYesNo)
If response = vbNo Then Exit Sub
'LR = Sheets("LIST").Range("D" & Rows.Count).End(xlUp).Row
LR = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row
For I = 1 To LR
    ShName = ActiveSheet.Range("D" & I).Value
    Sheets(ShName).PrintOut
Next I
End Sub

NickGB · Mar 24, 2016

Hello Sijpie
Apologies for rekindling an old post but this looks like it will do what I am looking for as well.

Firstly thanks for being explicit in your instruction, followed it to the letter and has (almost) worked like a dream.

Not sure if it is an excel 2016 issue or me being a tad special but the list box is not giving me the option to select multiple sheets.

Your help would be appreciated.

Thanks
Nick

sijpie · Mar 24, 2016

Hij Nick, Reading through my instructies i see i missed a step. Go to The userform and Click on The list box. Then look at the properties, in the list should be multiselect. Make sure it is set to true.

NickGB · Mar 24, 2016

sijpie said:
Hij Nick, Reading through my instructies i see i missed a step. Go to The userform and Click on The list box. Then look at the properties, in the list should be multiselect. Make sure it is set to true.

Thank you very much for such a quick reply. Much appreciated! I must really get my finger out and learn this stuff more.

Printing multiple sheets in a workbook with hundreds of sheets. From a list on a specific sheet.

Javi

Active Member

Excel Facts

sijpie

Well-known Member

Javi

Active Member

Javi

Active Member

NickGB

New Member

sijpie

Well-known Member

NickGB

New Member

Similar threads

Forum statistics

Share this page

Printing multiple sheets in a workbook with hundreds of sheets. From a list on a specific sheet.

Javi

Active Member

Excel Facts

sijpie

Well-known Member

Javi

Active Member

Javi

Active Member

NickGB

New Member

sijpie

Well-known Member

NickGB

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock