Moving Down a List Within a Macro

[lj]

In the following macro I repeat the same section about 50 times and it
would be convient to put this into a loop. However each time the
statement completes it uses a new paramter in the next section that
i've definined at the top of the VBA statement (seen below as sheet01,
sheet02). Is there anyway to move down this list in my statment using
a loop so that it automatically selects the appropriate row in my list
each time?

Sub Macro1()
'
'
book = Range("B10")
booknum = 1
Path = Range("B11")


Sheets("data").Select
Heading = Range("A1:L1")
Sheet01 = Range("A2:L51")
Sheet02 = Range("A52:L101")
Sheet03 = Range("A102:L151")
Sheet04 = Range("A152:L201")

--Section below is repeated 50 times
If Range("A2") <> "" Then
Worksheets.Add.Move
Range("A1:L1") = Heading
Range("A:A,C:C").Select
Selection.NumberFormat = "@"
Range("A2:L51") = Sheet01

End sub macro

Also is it possible to get the following statement to work? I want to
be able to name a parameter and then use it as a parameter within a
range to reference a specific cell, haven't been able to get this to
work so far, any suggestions?


Sub Macro2()

Red = 2
Range("B11")=Range("B"Red)

Endsub

 

[CLR]

You could put your "repeated" routine into a separate macro and "call" it
when you want it, like........

Sheets("data").Select
Heading = Range("A1:L1")
Sheet01 = Range("A2:L51")

Application.Run ("NameOfRepeatedRoutine")

Sheet02 = Range("A52:L101")

Application.Run ("NameOfRepeatedRoutine")

Sheet03 = Range("A102:L151")

Application.Run ("NameOfRepeatedRoutine")

Sheet04 = Range("A152:L201")

Application.Run ("NameOfRepeatedRoutine")

This way, you can "turn off" individual sections whenever you want just by
"Rem"ing out the line of code......

Vaya con Dios,
Chuck, CABGx3