Same action in different worksheets

[sa02000]

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]

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

[Earl Kiosterud]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Earl Kiosterud]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[sa02000]

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]

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