For Each ws in ActiveWorkbook.Worksheets loop trouble

[SBF12345]

Greetings,

I have the macro below giving trouble. The body of the macro will run, but does not want to seem to perform the task on a new sheet. It seems to be performing the same task on the same sheet (once for every sheet in the workbook)

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Range.("H1").Select
ActiveCell.value = "VR"
Range.("I1").Select
ActiveCell.value = "Average"

Nest ws

Any ideas why the loop isn't running the code for each worksheet in the workbook?

 

[Domenic]

You need to qualify your range references, for example...

ws.Range("H1").Select

However, there's no need to select the cell before entering a value. Try the following...

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Range("H1").Value = "VR"
        .Range("I1").Value = "Average"
    End With
Next ws

Hope this helps!

 

[SBF12345]

Great, just needed to qualify the range references, now its running through each sheet.

Thanks!

 

[Domenic]

You're very welcome!

Cheers!

 

[Rick Rothstein]

However, there's no need to select the cell before entering a value. Try the following...

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Range("H1").Value = "VR"
        .Range("I1").Value = "Average"
    End With
Next ws

Hope this helps!

Just to follow up... there is no need to loop through all of the sheets individually either. These two lines of code will put "VR" in cell H1 and "Average" in cell I1 on every sheet in the active workbook no matter what sheet is currently active.

Range("H1:I1") = Split("VR Average")
Sheets.FillAcrossSheets Range("H1:I1")