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!

Please state your question before groveling, groveling should go at the end of the post only. — awiebe
– awiebe, Commented Feb 3, 2017 at 6:16

A.S.H · Accepted Answer · 2017-02-03 06:25:25Z

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.

edited Feb 3, 2017 at 6:25

answered Feb 3, 2017 at 6:19

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3598756 · Accepted Answer · 2017-02-03 07:16:40Z

1

To both enhance performance and not to stress clipboard usage you can avoid Copy & PasteSpecial methods and switch to direct Value property assignments

furthermore you can condense ranges with proper syntax

as follows:

Option Explicit

Sub main()
    Dim ws As Worksheet, nr As Integer

    For Each ws In ThisWorkbook.Worksheets
        For nr = 14 To 309
            With ws
                .Range("C3:C4").Value = .Range("C" & nr).Resize(2).Value
                .Calculate
                .Range("E" & nr).Resize(, 2).Value = Array(.Range("F2").Value, .Range("I2").Value)
            End With
        Next
    Next
End Sub

answered Feb 3, 2017 at 7:16

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA: Nesting For Loop in For-Each Loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related