Looping through all the worksheets in a workbook

Question

This is my code to loop through all worksheets. I want to copy particular cell value from all the sheets and paste it to my master sheet. First i tried for one work sheet, its working fine. I modified the same code to loop through all the worksheets in the workbook. I displayed worksheets name and data which i want to copy. Its going to all the worksheets properly but i am not getting the data for worksheets except first sheet. Even its not displaying in msgbox except for first worksheet.

    For Each ws In ActiveWorkbook.Worksheets
    If Range("C3").Value = "" Then
    Coverge_ID = Range("C2")
    MsgBox Coverge_ID
    ThisWorkbook.Worksheets(2).Activate
    Range("A1").Offset(I, 0) = Coverge_ID
    I = I + 1
    Else
    Coverge_ID = Range("C3")
    MsgBox Coverge_ID
    ThisWorkbook.Worksheets(2).Activate
    Range("A1").Offset(I, 0) = Coverge_ID
    I = I + 1
    End If
    MsgBox ws.Name
    Next ws

When you use more that one sheets, you need to call Range with the Sheet reference. In your case ws.Range("C3").Value otherwise the code refer always to the "main" sheet (first in the code). Use ws. everywhere ... — user3514930

Gareth Gareth · Accepted Answer · 2014-07-24T11:35:11

Couple of things:

You're not actually referring to the sheet in the loop by qualifying Range() with your ws variable
You don't need to activate the worksheet to append a value to it

Try the below code instead:

Dim ws As Worksheet
Dim i As Long
Dim Coverage_ID As String

For Each ws In ActiveWorkbook.Worksheets
    If ws.Range("C3").Value = "" Then
        Coverage_ID = ws.Range("C2").Value
        MsgBox Coverage_ID
        ThisWorkbook.Worksheets(2).Range("A1").Offset(i, 0) = Coverage_ID
        i = i + 1
    Else
        Coverage_ID = ws.Range("C3").Value
        MsgBox Coverage_ID
        ThisWorkbook.Worksheets(2).Range("A1").Offset(i, 0) = Coverage_ID
        i = i + 1
    End If
    MsgBox ws.Name
Next ws

Looping through all the worksheets in a workbook

1 Answers