Excel sheet loop command not working

Question

Sub forEachWs()

Dim Ws As Worksheet
Windows("XYZSheet.xlsx").Activate
For Each Ws In ActiveWorkbook.Worksheets
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-2])"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=(RC[-1]/R1C5)"
    Range("D2").Select
    Selection.Copy
    Range("D2:D2450").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("D:D").Select
    Range("D2").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 Next Ws 
 End Sub

The code does not loop through the other tabs within the Sheets of Workbook "XYZSheet.xlsx" and I cannot seem to understand why.

The code in the middle from "Range(E1)" all the way to "ReplaceFormat:=False" are all through a recorded session with minor edits.

I don't fully understand VBA code apart from cycling through it with F8, so please go easy on me!

Thanks!

Was it in pasting? Your second line should be split into two at `.ActivateFor`, should be `.Activate`, then next line starts `For`...Also, I highly suggest reading through [how to avoid `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Jul 07 '16 at 14:03
You are not activating the sheets in your `For` loop, so the loop always run on the same sheet. That is one of the main reasons why `.Select`should be avoided as @BruceWayne said. — Vincent G, Jul 07 '16 at 14:18

score 0 · Answer 1 · answered Jul 07 '16 at 14:37

You need something like:

Sub forEachWs()

Dim Ws As Worksheet
Windows("XYZSheet.xlsx").Activate
For Each Ws In ActiveWorkbook.Worksheets
    With Ws
        .Range("E1").FormulaR1C1 = "=SUM(C[-2])"
        .Range("D2").FormulaR1C1 = "=(RC[-1]/R1C5)"
        .Range("D2").Copy Destination:=Range("D2:D2450")
        .Columns("D:D").Value = Columns("D:D").Value
        .Columns("D:D").Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows
    End With
Next Ws
End Sub

Excel sheet loop command not working

1 Answers1