Code help Printing multiple worksheets based on criteria

Javi

Active Member
Joined
May 26, 2011
Messages
440
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.






VBA Code:
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



VBA Code:
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

Active Member
Joined
May 26, 2011
Messages
440
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,968
Office Version
  1. 2007
Platform
  1. Windows
Try this for button PrintPreview

Rich (BB code):
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

Active Member
Joined
May 26, 2011
Messages
440
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"

1599617266445.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,968
Office Version
  1. 2007
Platform
  1. Windows
What does the error message say?
Do you have a hidden sheet?
 

Javi

Active Member
Joined
May 26, 2011
Messages
440

ADVERTISEMENT

Sorry I should've included this.

1599618946088.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,968
Office Version
  1. 2007
Platform
  1. Windows
That means one of the names in the listbox does not exist as a sheet. Check the names on the main sheet
 

Javi

Active Member
Joined
May 26, 2011
Messages
440
OOPS.....Thank you for sharing your knowledge. The code is working as required!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,968
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,148,140
Messages
5,745,036
Members
423,917
Latest member
Frank1931

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