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