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]

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"

 

[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 th
following actions on all the worksheets. here is the code that i a
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,
Ja

 

[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