Loop - for each sheet not working?

  • Thread starter Thread starter Shetty
  • Start date Start date
S

Shetty

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
 
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
 
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)
 
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
 
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
 
Back
Top