Solved: Printing an array of sheets

[BrianLong]

I am trying to code a button to print out an array of sheets (60+ sheets)

The current code that I was using on a similar project (few sheets) is as follows:
Sheets(Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)).PrintOut


Using this example I would have to list each sheet indivitually. This would not work for me as I would like to have the flexibility to add more sheets as needed. There is a sheet titled 'LAST' that I use to search for and set up loops. I was wondering if there was a way I would specify a range of sheets in the - Array() - portion.

Current code I am using is :
Dim PCLastWks As Integer

'Finds the worksheet titled "LAST" to set up array parameters
Do
PCLastWks = PCLastWks + 1
WksNameCheck = Worksheets(PCLastWks).Name
Loop Until WksNameCheck = "LAST"
sheets(array()).Printout

I do not know what to put in the array(). I want to print the range from Sheet(6) - Sheet(PCLastWks-1)

[gnod]

try this


Sub ShtPrintout()
Dim sht As Worksheet
Dim i As Integer, ctr As Integer

i = 2 ' set your starting sheet number (ex: start from sheet 2)
ctr = 1
For Each sht In Worksheets
If ctr >= i And sht.Name <> "LAST" Then
sht.PrintOut
End If
ctr = ctr + 1
Next
End Sub

[Charlize]

Maybe this ?Sub print_every_except_those_in_array()
Dim sh As Worksheet
Dim item As Variant
Dim inlist As Boolean
inlist = False
For Each sh In ThisWorkbook.Worksheets
For Each item In Array("one", "two", "three", "last")
If sh.Name = item Then
inlist = True
Exit For
End If
Next item
If inlist Then
MsgBox "don't print"
Else
MsgBox "print"
End If
inlist = False
Next sh
End Sub
Charlize

[BrianLong]

It appears that code would generate a print request for each page. I did some digging through the search function and found something that is functional for what I need. But I am still interested in knowing if there is a better way to do it.

Code I am using now :
Application.ScreenUpdating = False

Dim PCLastWks As Integer

PCLastWks = 6
'Finds the worksheet titled "LAST" to set up array parameters
Do
Sheets(PCLastWks).Select Replace:=False
PCLastWks = PCLastWks + 1
WksNameCheck = Worksheets(PCLastWks).Name
Loop Until WksNameCheck = "LAST"
Application.ScreenUpdating = True
ActiveWindow.SelectedSheets.PrintPreview 'Out

[lucas]

What exactly are you trying to do? Do you want to print every sheet in the workbook no matter how many have been added or do you want to print them all except for certain sheets by name or location.....

Do you wish to list all of the sheets and put a checkmark next to the ones you want printed.......

I'm just confused as to your objective.

[BrianLong]

I want to print from sheet 6 thru the sheet priot to the one named "LAST". As this range currently contains more than 60 sheets I would like to do it in one print command instead of individually doing a printout on each individual sheet.

[gnod]

are you trying to group the sheet? because you use the "Select"
i thought you were trying to print from your starting sheet up to the last sheet except if the name is "LAST"

kindly explain clearly what do you want..

[mdmackillop]

Sub PrintSheets()
Dim Arr()
ReDim Arr(0)
For i = 6 To Sheets.Count
If Sheets(i).Name = "Last" Then
ReDim Preserve Arr(j - 1)
Exit For
End If
Arr(j) = Sheets(i).Name
j = j + 1
ReDim Preserve Arr(j)
Next
Sheets(Arr).PrintOut
End Sub

[mdmackillop]

Slightly neater

Sub PrintSheets()
Dim Arr()
ReDim Arr(0)
For i = 6 To Sheets.Count
If Sheets(i).Name <> "Last" Then
Arr(j) = Sheets(i).Name
j = j + 1
ReDim Preserve Arr(j)
Else
ReDim Preserve Arr(j - 1)
Exit For
End If
Next
Sheets(Arr).PrintOut
End Sub

[mdmackillop]

For information, if you wish to print to PDF, you need to use the Array method, otherwise you will get a separate file for each sheet. Also required if you wish to use page numbering in Headers/Footers.

[BrianLong]

Looks like MdMac's code is what I was looking for. I am truly very sorry if i was confusing in my explinations. I was just trying to group all sheets into one print call instead of printing each sheet individually.