Changing Sheet("Name").Select Name

  • Thread starter Thread starter ccl28
  • Start date Start date
C

ccl28

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
 
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
Click to expand...
 
Dear Stefi,

Thanks for your reply.

The formulae are changing according to the sheet name.
Row Change when go from Inv1. Pin to Inv1. Large.

Example:
Inv1. Pin is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C5"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C9"

Inv1. Small is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C6"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C10"

Inv1. Medium is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C7"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C11"

Inv1. Large is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C8"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R20C12"

While
Column Change when go from Inv1. to Inv. 100

Example:
Inv2. Pin is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C5"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C9"

Inv2. Small is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C6"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C10"

Inv2. Medium is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C7"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C11"

Inv2. Large is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C8"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign
Prob'!R25C12"



How to change accordingly?


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. Ver
tedious (have to do few hundred time).

Anyone can help me simplify the below macro by only identify th
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
Click to expand...
 
Hi ccl28,

If I understood well the rules, the cell references can be generated this way:

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
genrow = 15 + shcounter * 5
gencol = 5 + sizecounter
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.Prob'!R" &
genrow & "C" & gencol
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.Prob'!R" &
genrow & "C" & gencol + 4

' your other code here

Next sizecounter
Next shcounter
End Sub

Regards,
Stefi

„ccl28†ezt írta:
Dear Stefi,

Thanks for your reply.

The formulae are changing according to the sheet name.
Row Change when go from Inv1. Pin to Inv1. Large.

Example:
Inv1. Pin is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C5"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C9"

Inv1. Small is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C6"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C10"

Inv1. Medium is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C7"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C11"

Inv1. Large is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C8"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R20C12"

While
Column Change when go from Inv1. to Inv. 100

Example:
Inv2. Pin is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C5"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C9"

Inv2. Small is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C6"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C10"

Inv2. Medium is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C7"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C11"

Inv2. Large is for
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C8"
ActiveCell.FormulaR1C1 = _
"='F:\Model Rebuilt 050706\[Explosion Probabilities.xls]Ign.
Prob'!R25C12"



How to change accordingly?


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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changing the row and column in R1C1 formulae 1
File Name changes with dates and Deleting Rows of Zeros 0
Sheet Naming, and code trimming. 6
Defining a range in many sheets 6
error: ActiveCell.Offset(0, -1).Select = Application-defined or object-defined error 7
subtotalling records 7
Refering to new sheet name 3
Windows XP Pivot table field names invalid 0

Back
Top