Excel VBA Worksheet loop - How to move through n worksheets

Question

Here is a simple loop that is copying a range to another location on the same worksheet. This also needs to loop through all the remaining worksheets and perform the same copy paste values. My use of variable "Dim ws" in the loop is suspect.

Sub UpdateSPCData()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Select Case UCase(wsLoop)
    Case "Data - MOAQ", "Report" 'Do nothing
    Case Else
        Range("H2:H5").Copy
        Range("I2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End Select
Next ws
End Sub

What is wsLoop? Your loop makes no reference to ws so will always refer to the active sheet. — SJR
That's a good question. It is borrowed code. Do you have a suggestion? — David Hawkins

SJR SJR · Accepted Answer · 2017-07-18T18:07:23

I think you need this. Also, if checking upper case names you must make sure you are comparing with upper case text.

Sub UpdateSPCData()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Select Case UCase(ws.Name)
    Case "DATA - MOAQ", "REPORT" 'Do nothing
    Case Else
        ws.Range("H2:H5").Copy
        ws.Range("I2").PasteSpecial Paste:=xlPasteValues
    End Select
Next ws

End Sub

Excel VBA Worksheet loop - How to move through n worksheets

1 Answers