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