For Each Worksheet Loop Not Moving To Next Worksheet

[SBF12345]

This chunk is not moving from ws to ws like you would expect. I don't know why. Any ideas?

For Each ws In wkbk_LI.Worksheets

    c = ActiveSheet.Name


    wkbk_LI.Worksheets(c).Cells.ClearContents


    'Dependents
    Set rng = wkbk_D.Worksheets(c).Range("A6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("A1").PasteSpecial Paste:=xlPasteValues


    Set rng = wkbk_D.Worksheets(c).Range("M6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("B1").PasteSpecial Paste:=xlPasteValues


    'Independents
    Set rng = wkbk_I.Worksheets(c).Range("FN6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("D1").PasteSpecial Paste:=xlPasteValues


    Set rng = wkbk_I.Worksheets(c).Range("FO6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("E1").PasteSpecial Paste:=xlPasteValues


    Set rng = wkbk_I.Worksheets(c).Range("CZ6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("F1").PasteSpecial Paste:=xlPasteValues


    Set rng = wkbk_I.Worksheets(c).Range("DA6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("G1").PasteSpecial Paste:=xlPasteValues


    Set rng = wkbk_I.Worksheets(c).Range("AF6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("H1").PasteSpecial Paste:=xlPasteValues


    Set rng = wkbk_I.Worksheets(c).Range("AJ6")
    Set rng = Range(rng, rng.End(xlDown))
    rng.Copy
    wkbk_LI.Worksheets(c).Range("I1").PasteSpecial Paste:=xlPasteValues


Next ws

 

[Fluff]

Try

For Each Ws In wkbk_LI.Worksheets

 C = ActiveSheet.Name

    Ws.Cells.ClearContents


    'Dependents
    Set Rng = wkbk_D.Worksheets(C).Range("A6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("A1").PasteSpecial Paste:=xlPasteValues


    Set Rng = wkbk_D.Worksheets(C).Range("M6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("B1").PasteSpecial Paste:=xlPasteValues


    'Independents
    Set Rng = wkbk_I.Worksheets(C).Range("FN6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("D1").PasteSpecial Paste:=xlPasteValues


    Set Rng = wkbk_I.Worksheets(C).Range("FO6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("E1").PasteSpecial Paste:=xlPasteValues


    Set Rng = wkbk_I.Worksheets(C).Range("CZ6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("F1").PasteSpecial Paste:=xlPasteValues


    Set Rng = wkbk_I.Worksheets(C).Range("DA6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("G1").PasteSpecial Paste:=xlPasteValues


    Set Rng = wkbk_I.Worksheets(C).Range("AF6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("H1").PasteSpecial Paste:=xlPasteValues


    Set Rng = wkbk_I.Worksheets(C).Range("AJ6")
    Set Rng = Range(Rng, Rng.End(xlDown))
    Rng.Copy
    Ws.Range("I1").PasteSpecial Paste:=xlPasteValues


Next Ws

 

[Joe4]

Looping through the collection of worksheets does not select or activate them.

You either need to preface your ranges with the ws variable, i.e.

Set rng = ws.Range("A1:A10")

or Select/Activate the sheet as the first step in the loop, i.e.

ws.Activate

 

[SBF12345]

I was trying to step through it with F8 with the first sheet in the set to loop through partly visible. After I watched the "c" variable i saw that it was in fact stepping through each sheet, but not making the progress visible. I suppose the moral of the story is to add a watch and know that watching the screen update is not fool proof.