# For Next Looping (custom variables)

terryspencer2003@yahoo.ca
Guest
Posts: n/a

 8th Jul 2003
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.

TS

Mark Bigelow
Guest
Posts: n/a

 8th Jul 2003
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
ActiveSheet.Name = "Sheet List"
Else
Sheets("Sheet List").Cells.Delete
End If
Err.Clear
On Error GoTo 0

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,

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

Sheets("Sheet List").Delete

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!

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post sprite scaler AMD 64 Bit 0 8th Feb 2008 02:43 AM sprite scaler ATI Video Cards 0 8th Feb 2008 02:43 AM sprite scaler Video Cards 0 8th Feb 2008 02:41 AM =?Utf-8?B?a29rYW5lZWdvbGQ=?= Microsoft Powerpoint 11 19th Jan 2005 07:14 PM GretOgrady Microsoft Excel Discussion 2 5th Jan 2005 09:29 PM

Features