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

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

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

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

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

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