CODEWITHSUNDEEP
vbaexcelloops
Emily I
Emily I

Reputation: 31

Loop for all worksheet in a workbook is not working

I would to copy paste the first cell of a sheet trough the last row for all sheets in a workbook and my code is not working, the code is done on the active sheet only.

Sub Macro5()
'
' Macro5

 Dim ws As Worksheet

 For Each ws In ActiveWorkbook.Worksheets

     Range("A2").Copy Destination:=Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row)

 Next ws

End Sub

Thanks for your help

Upvotes: 3

Views: 2408

Answers (1)

MatthewD
MatthewD

Reputation: 6761

Looping the worksheets does not make them active. You want something like this.

    Dim WS As Excel.Worksheet
    Dim iIndex As Integer

    For iIndex = 1 To ActiveWorkbook.Worksheets.count
        Set WS = Worksheets(iIndex)
        With WS

        'Do something here.
            .Range("A2").Copy Destination:=.Range("A3:A" & .Cells(.Rows.Count, "B").End(xlUp).Row)
        End With
    Next iIndex

You could use the ws in your loop, but I always set the object.

 For Each ws In ActiveWorkbook.Worksheets
     ws.Range("A2").Copy Destination:=ws.Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row)
 Next ws

Upvotes: 3

Related Questions