For Next Looping (custom variables)

[terryspencer2003]

I often use For Next Loops to loop through routines:

For X = 1 to 10

I know that you can also step through the loop based on a constraint:

For X = 1 to 10, Step 2 (you get 2 4 6 8 10)

But is it possible to set up the loop to get say (1,5,6,8,10). That
is I want to predefine the loop based on a custom order. I am
assuming that I have to read the range into some sort of array and
then give the user the option to custom pick. I imagine a userform is
needed.

And is it possible to do this with sheets in Excel. So if I have a
print macro which prints predefined sheets within a loop, can I set up
another loop which reads the number of sheets into my for Next
statement and allows me to custom pick the ones I want to print within
the greater loop? That is I pick the sheets in advance, and my macro
prints these chosen sheets with its loop.

Thanks in advance!

TS

 

[Mark Bigelow]

So, if I understand you correctly, you would like a macro that allows
you to define the order of printing of the sheets and then prints them
in that order. If so, here's what you can do:

1. This code will list all the sheets and ask you to input a print
order number in the cell to the right of that sheet name:

Sub ListSheets()

Dim intSht As Integer

Application.ScreenUpdating = False

' Create new sheet
On Error Resume Next
Sheets("Sheet List").Activate
If Err.Number <> 0 Then
Sheets.Add
ActiveSheet.Name = "Sheet List"
Else
Sheets("Sheet List").Cells.Delete
End If
Err.Clear
On Error GoTo 0

' Create headings
Range("A1").Value = "Sheet Name"
Range("B1").Value = "Print Order"

' List sheets (except Sheet List)
intSht = 2
For Each sht In Sheets
If sht.Name <> "Sheet List" Then
Range("A" & intSht).Value = sht.Name
intSht = intSht + 1
End If
Next sht

ActiveSheet.Cells.EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

2. Once you have determined the print order and typed it in, run this
macro.

Sub PrintInOrder()

Application.ScreenUpdating = False

' Make sure the list is there.
On Error Resume Next
Sheets("Sheet List").Activate
If Err.Number <> 0 Then
MsgBox ("Please run the ListSheets subroutine first.")
Exit Sub
End If
' Make sure the print order is input.
If WorksheetFunction.CountA(Range("B:B")) <>
WorksheetFunction.CountA(Range("A:A")) Then
MsgBox ("You must input all print orders. Please do so and
re-run this macro.")
Exit Sub
End If
On Error GoTo 0

Range("A:B").Sort Key1:=Range("B:B"), Order1:=xlAscending,
Header:=xlYes

For x = 2 To ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
Sheets(Range("A" & x).Value).Print
Next x

Application.DisplayAlerts = False
Sheets("Sheet List").Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

If you have any problems, please let me know.

Mark

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!