Loop - for each sheet not working?
 

I want a value to be written to same cell of all the sheets. I have
following macro written.
===========
Sub All_Sheets_Loop()
Dim i As Integer
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
End Sub
===========
Above macro writes only in the active sheet and other sheets are not
written by the value.
If I add a line activate.sheet it gives error.
Any thoughts pl?
Shetty

Loop - for each sheet not working?
 

ActiveSheet.Range("A1").Value = 10
Try
sh.Range("A1").Value = 10

"Shetty" wrote in message
om...
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
Above macro writes only in the active sheet and other sheets are not
written by the value.

Loop - for each sheet not working?
 

Shetty

Try this

Sub All_Sheets_Loop()
Dim i As Integer
Dim sh
i = Worksheets.Count
For sh = 1 To i
Sheets(sh).Range("A1").Value = 10
Next
End Sub

Regards
Peter
-----Original Message-----
I want a value to be written to same cell of all the
sheets. I have
following macro written.
===========
Sub All_Sheets_Loop()
Dim i As Integer
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
End Sub
===========
Above macro writes only in the active sheet and other
sheets are not
written by the value.
If I add a line activate.sheet it gives error.
Any thoughts pl?
Shetty
.

Loop - for each sheet not working?
 

Explanation

For Each sh in Worksheets
does not activate the sheet, that is why you cannot use ActiveSheet. It just
sets the sh object at the next sheet in the collection, thus you reference
the sheet through this object.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Sriram N A" wrote in message
...
ActiveSheet.Range("A1").Value = 10
Try
sh.Range("A1").Value = 10

"Shetty" wrote in message
om...
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
Above macro writes only in the active sheet and other sheets are not
written by the value.

Loop - for each sheet not working?
 

Sub All_Sheets_Loop()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Range("A1").Value = 10
Next

End Sub

'sh' is the worksheet that you want to work on. No need
for it to be the active sheet, and in your code,
activesheet refers to the er, active sheet only.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
I want a value to be written to same cell of all the
sheets. I have
following macro written.
===========
Sub All_Sheets_Loop()
Dim i As Integer
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
End Sub
===========
Above macro writes only in the active sheet and other
sheets are not
written by the value.
If I add a line activate.sheet it gives error.
Any thoughts pl?
Shetty
.

Loop - for each sheet not working?
 

I just created this using the macro recorder to select all sheets.
Just change one line as shown by removinging the one with the comment
Sub Macro1()
' Macro recorded 1/13/2004 by Don Guillett

' Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
sheets.select ' selects all instead of array above

Sheets("Sheet1").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = "3"
Sheets("Sheet2").Select
End Sub

--
Don Guillett
SalesAid Software

"Shetty" wrote in message
om...
I want a value to be written to same cell of all the sheets. I have
following macro written.
===========
Sub All_Sheets_Loop()
Dim i As Integer
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
End Sub
===========
Above macro writes only in the active sheet and other sheets are not
written by the value.
If I add a line activate.sheet it gives error.
Any thoughts pl?
Shetty

Loop - for each sheet not working?
 

It worked great. Thanks to all of you for your help.

Regards,



"Don Guillett" wrote in message ...
I just created this using the macro recorder to select all sheets.
Just change one line as shown by removinging the one with the comment
Sub Macro1()
' Macro recorded 1/13/2004 by Don Guillett

' Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
sheets.select ' selects all instead of array above

Sheets("Sheet1").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = "3"
Sheets("Sheet2").Select
End Sub

--
Don Guillett
SalesAid Software

"Shetty" wrote in message
om...
I want a value to be written to same cell of all the sheets. I have
following macro written.
===========
Sub All_Sheets_Loop()
Dim i As Integer
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Range("A1").Value = 10
Next
End Sub
===========
Above macro writes only in the active sheet and other sheets are not
written by the value.
If I add a line activate.sheet it gives error.
Any thoughts pl?
Shetty