Worksheet Loop Help Please

[My Aswer Is This]

I want to put the word "George" in cell A1 of every Worksheet in my Workbook. Why does this script not work for me?

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
         WS_Count = ActiveWorkbook.Worksheets.Count
         For I = 1 To WS_Count
         Cells(1, 1) = "George"
         Next I
End Sub

 

[Alan_P]

Hi,

You need to ref the sheet...

Sheets(I).Cells(1,1).value = "George"

Cheers,
Alan.

 

[My Aswer Is This]

Thanks Alan_P
Works perfect.

 

[Peter_SSs]

Also, a simpler way is to reference the worksheets directly rather than through the use of the counter I

Sub WorksheetLoop()
  Dim WS As Worksheet
  
  For Each WS In Worksheets
    WS.Cells(1, 1).Value = "George"
  Next WS
End Sub

 

[My Aswer Is This]

Thanks Peter.
I'm always looking for the best way to do things. I previously had been using loops and activate sheet but this works a lot better I'm sure. I enjoy learning more here every day.

 

[My Aswer Is This]

Peter, one last question. How would I do this if I had 20 worksheets in my workbook and only want to run the loop on the first 10 sheets?

 

[farmerscott]

Try-

Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
         WS_Count = 10
         For I = 1 To WS_Count
         Sheets(I).Cells(1, 1) = "George"
         Next I
End Sub

Note: this will operate on the first 10 sheets found on your tab order.

Hope this helps,

FarmerScott

 

[My Aswer Is This]

That's a great answer Farmerscott. Why did I not think of that?

 

[Peter_SSs]

Again, a little simpler would be to not bother with the WS_Count variable as it takes two lines to set up and only gets used once.

Note also that I have declared I as Long, not Integer.
vba converts Integer Variables to Long Variables before working with them so you might as well ..
- Save vba that conversion process, and
- Save typing 3 letters in your declaration line.

Sub WorksheetLoop()
  Dim I As Long
  
  For I = 1 To 10
    Sheets(I).Cells(1, 1).Value = "George"
  Next I
End Sub

 

[My Aswer Is This]

Thanks again Peter. Good script.