Print Multiple Sheet from User Input Using Excel VBA

I have a code which will take user input from a list box, which is the name of all the tabs I have in the workbook, selected name will be moved to another listbox, say Listbox2. This list box 2 items are being converted to an array to go to the print preview page. but the problem is, I'm doing it in a loop, so I can't print them in one go. If I select 5 tabs, I'm having to print 5 tabs separately. I want to print them together. I've tried putting the print preview command out of the loop, but then I get an error saying application not found or something similar. Here's the code below:

Private Sub Printout1_Click()
Dim Size As Integer

    Size = Me.ListBox2.ListCount - 1
ReDim ListBoxContents(0 To Size) As String

    Dim i As Integer

  For i = 0 To Size
      ListBoxContents(i) = Me.ListBox2.List(i)

Next i

For i = 0 To Size
Sheets(ListBoxContents(i)).Visible = True
Sheets(ListBoxContents(i)).Select
    ActiveWindow.SelectedSheets.PrintPreview
    Sheets(ListBoxContents(i)).Visible = False
Next i


End Sub

1 Answer

  1. Donald- Reply

    2019-11-14

    You can copy all the sheets in another sheet just for print , For exemple if he select sheet1 and sheet2

    You can use a sheet called PRINTSHEET

    and them Copy the Sheet1.usedrange and Sheet2.usedrange to PrintSheet

    after that u can just print the PrintSheet

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>