Try this:

Sub Macro1()
Dim sizes(4)
sizenames = Array("Pin", "Small", "Medium", "Large")
For shcounter = 1 To 100
For sizecounter = 0 To 3
currshname = "Inv." & shcounter & " " & sizenames(sizecounter)
Sheets (currshname).Select

' your code here

Next sizecounter
Next shcounter
End Sub

This code doesn't contain "_DD" at the end of sheet names because you didn't
mention in your explanation that it'a a constant part of the name or
something else!

Regards,
Stefi


€žccl28€� ezt Ã*rta:


Hi,

I have 100 sheet name that begin with Inv. 1 ... to Inv.100 ...
example: Inv.1 Pin, Inv.1 Small, Inv.1 Medium, Inv.1 Large.

The value of the name is increasing in term of Inv. i+1 (i=1).

What I did was using replace when the sheet name is changing. Very
tedious (have to do few hundred time).

Anyone can help me simplify the below macro by only identify the
beginning name of the sheet or any suggestion?


Sub Macro1()

Sheets("Inv.33 Pin_DD").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C5"
Range("C13").Select
Selection.Interior.ColorIndex = 34
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C9"
Range("E24").Select
ActiveSheet.Shapes("Line 40").Select
Range("E23").Select
Selection.Interior.ColorIndex = 34

Sheets("Inv.33 Small_DD").Select
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C6"
Range("C13").Select
Selection.Interior.ColorIndex = 34
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C10"
Range("E23").Select
Selection.Interior.ColorIndex = 34

Sheets("Inv.33 Medium_DD").Select
Range("C13").Select
Selection.Interior.ColorIndex = 34
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C7"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C11"
Range("E23").Select
Selection.Interior.ColorIndex = 34

Sheets("Inv.33 Large_DD").Select
Range("C13").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C8"
Range("C13").Select
Selection.Interior.ColorIndex = 34
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C12"
Range("E23").Select
Selection.Interior.ColorIndex = 34
End Sub


--
ccl28
------------------------------------------------------------------------
ccl28's Profile: http://www.excelforum.com/member.php...o&userid=36095
View this thread: http://www.excelforum.com/showthread...hreadid=558758