any suggestions welcomed

  • Thread starter Thread starter jonny
  • Start date Start date
J

jonny

re 8:10 am

how do you go about populating an array one by one? I'm
assuming I could check each of the 160 sheets and if one
needs printing, put the sheet no. into the array and
print the sheets(array).printout function

maybe
 
"...And the next sign of the Apocalypse will be..."
*****
re 8:10 am

how do you go about populating an array one by one? I'm
assuming I could check each of the 160 sheets and if one
needs printing, put the sheet no. into the array and
print the sheets(array).printout function

maybe
Click to expand...

Here's two different ideas I had, which I did before seeing your second
post. This works on my computer with Sheets.Count=4.
Sub foo()
ReDim printThese(1 To Sheets.Count) As Boolean
For x = 1 To Sheets.Count
If Sheets(x).Cells(1, 3).Value = "printable" Then
printThese(x) = True
Next
For x = 1 To UBound(printThese)
If printThese(x) Then Sheets(x).PrintOut
Next
End Sub

Here's an alternate version I tried - it *doesn't* work but I think it
would be a trivial matter to fix. I'm not going to; I'm going to sleep.
Sub bar()
ReDim printThese(0) As Integer '0 is unused in this example
For x = 1 To 4
If Sheets(x).Cells(1, 3).Value = "printable" Then
ReDim printThese(UBound(printThese) + 1)
printThese(UBound(printThese)) = x
End If
Next
For x = 1 To UBound(printThese)
'subscript out of range on x=1 - shrug
Sheets(printThese(x)).PrintOut
Next
End Sub
 
Why do you want them in a Array?

You can loop and print the sheets one by one

Sub test()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Range("C1").Value = "printable" Then sh.PrintOut
Next sh
End Sub
 
jonny

Here's an example

Sub test()

Dim arrSheets() As String
Dim Sh As Worksheet
Dim i As Long

i = 1

For Each Sh In ThisWorkbook.Worksheets
If Sh.Range("A1").Value = 1 Then
ReDim Preserve arrSheets(1 To i)
arrSheets(i) = Sh.Name
i = i + 1
End If
Next Sh

ThisWorkbook.Sheets(arrSheets).PrintOut

End Sub
 
Back
Top