VBA Loop Worksheets not working

[Joey_Ng]

Hi,

I am trying to loop through each worksheet in a workbook but the code only works for the activesheet and not looping through the other sheets. I tried both below codes but neither works.

Sub test()
Dim ws As Worksheet
For Each ws In Sheets
Range("A1") = "test"
Next ws
End Sub

OR

Sub test2()
Dim i As Integer
For i = 2 To Worksheets.Count
Range("A1") = "test"
Next i
End Sub

Any idea what the issue is? The code works fine for the activesheet but it won't move to the next sheet so it repeats the actions for the activesheet.

MR Excel - Google Drive

drive.google.com

Many Thanks,

Joseph

 

[dmt32]

you need to qualify the Range to the ws object variable

Sub test()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.Range("A1") = "test"
    Next ws
End Sub

Dave

 

[Joey_Ng]

Thanks Dave. That works.

Revised code below also works.
Sub test2()
Dim i As Integer
For i = 2 To Worksheets.Count
Sheets(i).Range("A1") = "test"
Next i
End Sub

Much appreciated.

 

[dmt32]

Sub test2()
Dim i As Integer
For i = 2 To Worksheets.Count
Worksheets(i).Range("A1") = "test"
Next i
End Sub

Advisable to refer to Worksheets in your code as Sheets will refer to both worksheet & chart sheets

Dave