Code help Printing multiple worksheets based on criteria

[Javi]

Hi All,

The main objective is to print multiple worksheets from a list on worksheet “Main” Col “C” that has an indicator of true appears in Col “AS”. The user does not necessarily need to see the full list of worksheets only items that are marked in Col “AS” TRUE. I spent much time trying to do this in a user form NO LUCK. I am open for different directions.

Below is my best attempt so far.

I have created an activeX control listbox (On Worksheet "Print_Select") that pulls all of the sheet names into a listbox and allows me to select which sheets I would like to print (see code below works well i.e. multiple sheets etc). I expect have about 100 sheets. On the worksheet "MAIN" each Row represents a worksheet. The name for each worksheet is created on the WS "MAIN" on its specific row, then copied to a cell on each worksheet with code to rename the worksheet. The main list also contains the indicator whether or not the worksheet should be printed. (Problem only sheet names to select from, I do not get my indicator whether to print or not)

I am looking for help/advice on the code to pull in data from worksheet “Main” Column AS that is associated with each worksheet. The "AS" column contains true, false indicating whether or not to print (Starting "AS5"). The sheet names are located on worksheet “MAIN” starting column "C5" (This is identical to all of the specific sheet names).


Thank you in advance for any assistance.

Sub Print_Sh1()

Dim i As Long, c As Long
Dim SheetArray() As String

With ActiveSheet.ListBoxSh
  For i = 0 To .ListCount - 1
      If .Selected(i) Then

        ReDim Preserve SheetArray(c)
          SheetArray(c) = .List(i)
               c = c + 1

End If
Next i

End With

'Sheets(SheetArray()).PrintPreview
If Application.Dialogs(xlDialogPrinterSetup).Show = True Then
Sheets(SheetArray()).PrintOut

End If

End Sub

Private Sub Worksheet_Activate()
Dim Sh
Me.ListBoxSh.Clear
For Each Sh In ThisWorkbook.Sheets
Me.ListBoxSh.AddItem Sh.Name
Next Sh
End Sub

 

[Javi]

I just reread I just read my last post and I apologize I don't think it makes any sense.

Bottom line is the code you prepared for me is for print out I need the same for print preview without pulling up the printer select dialog box. I tried to modify what you provided to accommodate this without success see above.

 

[DanteAmor]

Try this for button PrintPreview

Sub Print_preview()
  Dim i As Long, c As Long
  Dim SheetArray() As String
  
  With ActiveSheet.ListBoxSh
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve SheetArray(c)
        SheetArray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
  
  If c > 0 Then
      Sheets(SheetArray()).PrintPreview
  Else
    MsgBox "Select Sheets To Print"
  End If
End Sub

 

[Javi]

It worked when nothing was selected gave me the message "Select Sheets To Print"

When sheets were selected it failed on this line "Sheets(SheetArray()).PrintPreview"

 

[DanteAmor]

What does the error message say?
Do you have a hidden sheet?

 

[Javi]

Sorry I should've included this.

 

[DanteAmor]

That means one of the names in the listbox does not exist as a sheet. Check the names on the main sheet

 

[Javi]

OOPS.....Thank you for sharing your knowledge. The code is working as required!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.