For Each Worksheet in Workbook

Question

Any thoughts on why the below code would not be looping through the worksheets?

I'm trying to set a column in each sheet based on what the sheet name is. It's getting stuck on the active worksheet, and ignoring the If ws.Name <> "Default". This is built as a module:

Sub LangID_update()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim LastCol As Integer
Dim LastRow As Integer
Dim rng As Range

Application.ScreenUpdating = False

For Each ws In wb.Worksheets
If ws.Name <> "Default" Then

LastCol = ws.Cells(1, Columns.count).End(xlToLeft).Column
LastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
Set rng = Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1))
    With rng
        For Each c In Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1))
            If ws.Name = "ARGS" Then c.Value = "ESP"
            If ws.Name = "AUTS" Then c.Value = "GR"
            If ws.Name = "DEUS" Then c.Value = "GR"

        Next c
    End With
    End If
Next

Application.ScreenUpdating = True
Set wb = Nothing
Set ws = Nothing
Set rng = Nothing

End Sub

score 4 · Answer 1 · answered Oct 09 '15 at 14:50

4

Many of the object refences that you are using are unqualified and therefore refer to the active sheet. Add the ws. object qualification at the beginning of each object.

answered Oct 09 '15 at 14:50

Excel Hero

14,253
4
33
40

score 3 · Answer 2 · answered Oct 09 '15 at 14:50

You would probably want to explicitly declare your rng range's worksheet. (I'm assuming it's going to be in the ws).

Try this:

Sub LangID_update()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim LastCol As Integer
Dim LastRow As Integer
Dim rng As Range

Application.ScreenUpdating = False

For Each ws In wb.Worksheets
If ws.Name <> "Default" Then

LastCol = ws.Cells(1, Columns.count).End(xlToLeft).Column
LastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
Set rng = ws.Range(ws.Cells(2, LastCol + 1), ws.Cells(LastRow, LastCol + 1))
    With rng
        For Each c In rng
            If ws.Name = "ARGS" Then c.Value = "ESP"
            If ws.Name = "AUTS" Then c.Value = "GR"
            If ws.Name = "DEUS" Then c.Value = "GR"

        Next c
    End With
    End If
Next ws

Application.ScreenUpdating = True
Set wb = Nothing
Set ws = Nothing
Set rng = Nothing

End Sub

Edit: I'm also pretty sure you don't need the with rng since you loop through it with a for loop, and don't necessarily use rng.____ in the With statement.

score 1 · Answer 3 · answered Oct 09 '15 at 14:51

That is because you are not referencing the ws variable when accessing the ranges.

Set rng = Range(ws.Cells(2, LastCol + 1), ws.Cells(LastRow, LastCol + 1))
For Each c In rng

Notice: when you dont add a sheet qualification for the ranges and cells, they are taken from the ActiveSheet. That is why your code was stuck on the ActiveSheet.

score 0 · Answer 4 · answered Oct 09 '15 at 19:04

I think your For Each line is bugged, but without seeing what's behind, it's hard to say.

For Each ws In wb.Worksheets
If ws.Name <> "Default" Then

LastCol = ws.Cells(1, Columns.count).End(xlToLeft).Column
LastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
'next line is useless
'Set rng = Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1))
    With rng
        '             qualify Range and Cells
        For Each c In ws.Range(ws.Cells(2, LastCol + 1), ws.Cells(LastRow, LastCol + 1))
            If ws.Name = "ARGS" Then c.Value = "ESP"
            If ws.Name = "AUTS" Then c.Value = "GR"
            If ws.Name = "DEUS" Then c.Value = "GR"

        Next c
    End With
    End If
Next

score -1 · Answer 5 · answered Oct 09 '15 at 18:59

The answers thus far have been spot on: the problem is the lack of qualification for rng in the for block. That said though, a quick addition of one line will solve the issue:

If ws.Name <> "Default" Then ws.Activate

Activating the worksheet will make the rest of the code go fine, since you're on/thinking about the active sheet, and you just made that one light up.

For Each Worksheet in Workbook

5 Answers5

Linked