Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Same action in different worksheets
I am trying to do same action in all the sheets of an open workbook. I started with simple code below but it does not work. I get an error "type mismatch" at For Each sht In Worksheets Sub tryabc() Dim sht As Worksheets For Each sht In Worksheets Range("I1").Select ActiveCell.FormulaR1C1 = "Status" Next sht End Sub Thanks Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=475119 |
#2
|
|||
|
|||
Well I figured out part of the problem and atleast now it does not give me an error but now it only works for one worksheet and does not do same action on all worksheets I have in the workbook. Here is the code... Help Please!! Sub tryabc() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets Range("I1").Select Next sh End Sub -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=475119 |
#3
|
|||
|
|||
Jay,
I think it's a typo. You dimmed sht as Worksheets, which is a collection. Dim sht as Worksheet -- Earl Kiosterud www.smokeylake.com "sa02000" wrote in message ... I am trying to do same action in all the sheets of an open workbook. I started with simple code below but it does not work. I get an error "type mismatch" at For Each sht In Worksheets Sub tryabc() Dim sht As Worksheets For Each sht In Worksheets Range("I1").Select ActiveCell.FormulaR1C1 = "Status" Next sht End Sub Thanks Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=475119 |
#4
|
|||
|
|||
Jay,
Then it occurs to me that ActiveCell will not be the sht worksheet you're looping through, since you haven't selected it. Better yet, do it directly (without bothering to select the sheet): sht.Range("I1").Value = "Status" -- Earl Kiosterud www.smokeylake.com "sa02000" wrote in message ... I am trying to do same action in all the sheets of an open workbook. I started with simple code below but it does not work. I get an error "type mismatch" at For Each sht In Worksheets Sub tryabc() Dim sht As Worksheets For Each sht In Worksheets Range("I1").Select ActiveCell.FormulaR1C1 = "Status" Next sht End Sub Thanks Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=475119 |
#5
|
|||
|
|||
Yeah I changed Dim sht as worksheets to Dim sht as worksheet Now atleast I don't get the error but still it does not perform the following actions on all the worksheets. here is the code that i am working with. Please help!! Sub tryabc() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets Range("I1").Select ActiveCell.FormulaR1C1 = "Status" Next sh End Sub Thanks, Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=475119 |
#6
|
|||
|
|||
Well I think I figured it out. I had to add sh.activate before Range("I1").Select line Thank you Eric. The reason I did not want to do it direct (as you suggested) was because I know very soon I will want to do lot of same actions on all worksheets. Sub tryabc() Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets sh.activate Range("I1").Select ActiveCell.FormulaR1C1 = "Status" Next sh End Sub Thanks again. This forum ROCKS!! Jay -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=475119 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print order of worksheets | Excel Discussion (Misc queries) | |||
Changing a Link Mid-way Across Worksheets | Excel Worksheet Functions | |||
Excel needs to have the ability to insert "SUB" worksheets | Excel Worksheet Functions | |||
Adding multiple worksheets | Excel Worksheet Functions | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |