vba - Collecting an array of sheets based on conditions

[SloppyJ]

Hi ppl,

I need a code to select sheets with pages less than 15 to print out.
Also, a code to print out just the first 5 and last 5 pages if its more than 15 pages.

I've tried this so far but no success:

For X = 1 To Worksheets.Count

If Y Is Nothing Then
Set Y = Sheets(X)

Else
If ExecuteExcel4Macro("Get.Document(50)") < 10 Then
Set Y = Union(Y, Sheets(X))


End If
End If


Next X
Y.Select

 

[SloppyJ]

Bump pls!

 

[Sektor]

Try this.

Option Explicit

Sub SelectSheetsWithLessThan15Pages()

    Dim sh As Worksheet, arr() As Integer, i As Integer
    
    For Each sh In Worksheets
        If sh.PageSetup.Pages.Count <= 15 Then
            i = i + 1
            ReDim Preserve arr(1 To i)
            arr(i) = sh.Index
        End If
    Next

    Sheets(arr).Select

End Sub

Sub PrintSheetsWithMoreThan15Pages()

    Dim sh As Worksheet
    
    For Each sh In Worksheets
        With sh.PageSetup.Pages
            If .Count > 15 Then
                sh.PrintOut From:=1, To:=5
                sh.PrintOut From:=.Count - 5, To:=.Count
            End If
        End With
    Next

End Sub

 

[SloppyJ]

Wow ur a genius! It works!
Thanks!

 

[Teuteu]

Hi people,

firstly I must say that I only begin writing macros.
I want to create a macro which will be able to check in all sheets of a same workbook if the cell A1 is <> 0. And if so then collect it and check the sheet after, etc... At the end I want to print "as an array of sheets" all "printable" sheets in a unique pdf.

I try several codes without any success.

Someone has an idea ?

Thanks a lot in advance !

Teuteu !

 

[Teuteu]

Sorry I forgot, here is my code :
Dim mysht(), ib As Byte, sht As Worksheet

Sub Print1()

For Each sht In Worksheets
If sht.Range("A1").Value = "PRINT" Then
ReDim Preserve mysht(ib)
mysht(ib) = sht.Name
Debug.Print sht.Name
ib = ib + 1
End If
Next sht

Application.ActivePrinter = "FreePDF on Ne02:"

Sheets(Array(mysht)).PrintOut Copies:=1, Collate:=True

End Sub

 

[Teuteu]

Hello I worked again on my macro and I got better results. I define the "printable worksheet" as variables and it works. The only problem appears at the end of the macro when it should print the worksheet array, even if the worksheet are recognized. Here is my new code (I put the blocking part in Bold). Can someone help me please?

Sub SelectSheet()

Dim NumberOfSheetName As Integer
Dim ArraySheetName(0 To 50) As String
Dim ArrayPrintSheetName(0 To 50) As String
Dim StringPrintSheetName As String

A = -1
Set SheetNameLocation = Sheets("Input").Range("F6")
NumberOfSheetName = SheetNameLocation.End(xlDown).Row - 5

For I = 0 To NumberOfSheetName - 1
ArraySheetName(I) = SheetNameLocation.Offset(I)
Sheets(ArraySheetName(I)).Select
If Sheets(ArraySheetName(I)).Range("A1") = "PRINT" Then
A = A + 1
If A = 0 Then
StringPrintSheetName = ArraySheetName(I)
Else: StringPrintSheetName = Left$(StringPrintSheetName, Len(StringPrintSheetName)) & Chr(34) & ", " & Chr(34) & ArraySheetName(I) & Mid$(StringPrintSheetName, Len(StringPrintSheetName) + 1)
End If
End If
Next I

Sheets(Array(StringPrintSheetName)).Select
Application.ActivePrinter = "FreePDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"FreePDF on Ne02:", Collate:=True

End Sub

Thanks a lot in advance for any help !

Teuteu