Does not Loop like it is supposed to

[KAOS137]

I have the following code and I want it to loop through all sheets in the workbook except a sheet called "Summary".

Here is the code:
Sub DragSort()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

lastrow = Range("A3").End(xlDown).Row
Range("H3:I3").AutoFill Destination:=Range(Range("H3"), Range("I" & lastrow))


ActiveSheet.Name = ActiveSheet.Range("I3") & "." & Range("H3")


Next ws



End Sub

 

[Rick Rothstein]

I have the following code and I want it to loop through all sheets in the workbook except a sheet called "Summary".

Here is the code:
Sub DragSort()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

lastrow = Range("A3").End(xlDown).Row
Range("H3:I3").AutoFill Destination:=Range(Range("H3"), Range("I" & lastrow))

ActiveSheet.Name = ActiveSheet.Range("I3") & "." & Range("H3")

Next ws
End Sub

As you loop through the worksheets, they are not activated, so you need to have each range call reference the ws variable that is iterating the worksheets. So, the first line in the For..Next loop should look like this...

lastrow = ws.Range("A3").End(xlDown).Row

You need to do this same thing to each range call within the For..Next loop that is meant to apply to the currently iterated worksheet (if the ws. reference is omitted, the range call is to the active worksheet).

 

[KAOS137]

So I made some changes but I don't think it helped. I get an error code.


-- removed inline image ---


Sub DragSort()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

lastrow = ws.Range("A3").End(xlDown).Row
ws.Range("H3:I3").AutoFill Destination:=ws.Range(Range("H3"), Range("I" & lastrow))


ActiveSheet.Name = ActiveSheet.Range("I3") & "." & Range("H3")


Next ws



End Sub