VBA: Nesting For Loop in For-Each Loop

Question

I am trying to do a copy-paste loop within a worksheet and then apply this same loop to all other worksheets within the same workbook.

However, the problem is that the code does not move to the next worksheet after the first copy-paste loop is done on one worksheet, the loop just repeats itself in the same worksheet.

For Loop: Simple Copy-Paste

For-Each Loop: Loop through worksheets

This is the code:

Sub Code()

Dim sheet As Worksheet
Dim nr As Integer

For Each sheet In Worksheets

    For nr = 14 To 309

        Range("C" & nr).Copy
        Range("C3").PasteSpecial xlPasteValues

        Range("D" & nr).Copy
        Range("C4").PasteSpecial xlPasteValues

        ActiveSheet.Calculate

        Range("F2").Copy
        Range("E" & nr).PasteSpecial xlPasteValues

        Range("I2").Copy
        Range("F" & nr).PasteSpecial xlPasteValues

    Next nr

Next

End Sub

Thank you for your help!

Answer 1

You only need to qualify your ranges to make them reference to worksheet is question.

Dim sh as Worksheet, nr as Long
For Each sh In ThisWorkbook.Worksheets
    For nr = 14 To 309
        with sh
            .Range("C" & nr).Copy
            .Range("C3").PasteSpecial xlPasteValues
            .Range("D" & nr).Copy
            .Range("C4").PasteSpecial xlPasteValues
            .Calculate
            .Range("F2").Copy
            .Range("E" & nr).PasteSpecial xlPasteValues
            .Range("I2").Copy
            .Range("F" & nr).PasteSpecial xlPasteValues
        End with
    Next nr
Next sh

It's good practice to always qualify your ranges. Otherwise they go to whatever sheet is currently active.