selecting multiple sheets

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I am trying to select multiple sheets according to whether
they have any information on them. Ithen want to use the
printdialog box so that the individual can select his
printer.
I have figured out how to close the unnecessary sheets so
if I could get the "entire workbook" radio button to be
clicked upon opening the dialogs box that would work. I
looked through all of the arguments and I see a print_what
argument is available but I cannot seem to figure out what
words to use.
I can make if statements and find the sheets, but the only
way I can get the sheets to print is with Sheets(Array
(?)).Select. I cannot figure out how to change the array
depending on whether I want to select that sheet or not.
Please help if you can.
Dennis
 
Once you get the sheets selected/chosen, why not just print it (well, if you
don't have to let the user change printers...)

You can do it by selecting sheets:

Option Explicit
Sub testme01()

Dim AlreadySelected As Boolean
Dim wks As Worksheet

AlreadySelected = False
For Each wks In Worksheets
If wks.Range("a1").Value = 21 Then
wks.Select Not AlreadySelected
AlreadySelected = True
End If
Next wks

If AlreadySelected = False Then
MsgBox "nothing selected"
Else
ActiveWindow.SelectedSheets.PrintPreview '.printout
End If

End Sub


Or you could just keep track of the sheets yourself (and not select them):

Option Explicit
Sub testme02()

Dim mySheetNames() As String
Dim wksCtr As Long
Dim iCtr As Long

iCtr = 0
For wksCtr = 1 To Worksheets.Count
If Worksheets(wksCtr).Range("a1").Value = 1 Then
iCtr = iCtr + 1
ReDim Preserve mySheetNames(1 To iCtr)
mySheetNames(iCtr) = Worksheets(wksCtr).Name
End If
Next wksCtr

If iCtr = 0 Then
MsgBox "nothing selected"
Else
Worksheets(mySheetNames).PrintPreview '.printout
End If

End Sub
 
Back
Top