Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Changing Sheet("Name").Select Name
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Changing Sheet("Name").Select Name

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,646
Default Changing Sheet("Name").Select Name
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Changing Sheet("Name").Select Name

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?



ccl28 Wrote:
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


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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,646
Default Changing Sheet("Name").Select Name
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?



ccl28 Wrote:
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect sheet unchecking "Select locked cells" has undesiredresults wal Excel Discussion (Misc queries) 1 September 1st 08 03:28 PM
Questionnaire sheet: Select "yes" or "no," and only one can be selected bpatterson Excel Worksheet Functions 2 April 13th 06 11:04 PM
Changing "returned" values from "0" to "blank" LATATC Excel Worksheet Functions 2 October 20th 05 04:41 PM
Disable "select sheet" prompt jrfalck Excel Programming 3 November 22nd 04 05:57 PM
Using "Cells" to write "Range("A:A,H:H").Select" Trip Ives[_2_] Excel Programming 3 June 5th 04 03:13 PM


All times are GMT +1. The time now is 05:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement