|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com