Code troubleshooting

[PaulM2009]

Trying to figure out why this code isn't working. From what I can tell it is going through sheet 3 fine, but stopping and not going to 4 and so on.

Sub RoysterReport()
Dim LR As Long, ALR As Long, i As Long
Dim ws As Integer
Dim wsEnd As Integer

Application.ScreenUpdating = False

ws = 3
wsEnd = Worksheets.Count
    Do Until ws = wsEnd
        
           
   
    
        Worksheets(ws).Activate

        LR = Worksheets(ws).Cells(65536, 1).End(xlUp).Row
            For i = LR To 1 Step -1
                If Cells(i, 18) > 0 Then
                    ALR = Worksheets("Incidents").Cells(65536, 1).End(xlUp).Row + 1
                    Range(Cells(i, 1), Cells(i, 31)).Copy Destination:=Worksheets("Incidents").Range("A" & ALR)
                End If
            Next i
            
            
        ws = ws + 1
        
    Loop
    
    Worksheets(2).Activate
    
    Range("b:b,d:q,s:ae").Select
    Selection.Delete Shift:=xlToLeft
    Range("a1").Activate
    
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[stanleydgromjr]

PaulM2009,

Try this to watch the code, counters, and sheets.

In the worksheet, minimize the screen, so that the worksheet is on the left half of the screen.

Press and hold down the 'ALT' key, and press the 'F11' key, to launch the VBA Editor.

Make sure that you can view your code, and click on "View" "Locals Window".

Move the vertical seperator bar to the left, and then minimize the VBA screen, as the worksheet screen, and move it to the right.

Then put the cursor in your code, and press the F8 key to step thru the code, see your variables in the "Locals Window", and watch what is happening in the worksheet screen.

Watch the worksheet screen, and the "Locals Window" - your variables.

To stop stepping thur your code, click on "Run", "Reset".


Have a great day,
Stan

 

[ChrisM]

Code looks fine to me. Are you sure that worksheet(3) is the one you want to start with? Just because it's called Sheet3 in code doesn't mean it shows up in the worksheets collection as Worksheets(3).

Go to your immediate window and type:

Print Worksheets(3).Name

hit enter and see what you get

 

[PaulM2009]

Worksheets are as follows:
1- Agent Pay
2- Incidents
3- 1
4- 2
5- 3

And so on to 30.

Why it is running the code for sheet 3 and not looping past it has be baffled at the moment.

 

[PaulM2009]

Sorry, forgot to answer your question. I did what you advised and it returned "1".

 

[ChrisM]

This code works fine for me. You need to follow Stanley's advice and step through your code. No need to get too fancy with it though, just:

1. Set a breakpoint on your Application.screenupdating line, by clicking off to the left of the line, in the grey column.

2. Run your macro, it will stop at that first line.

3. Repeatedly push F8 and watch your code execute. Hover your mouse over variables to see what value they are holding.

 

[PaulM2009]

I somehow missed the post about stepping through the code. Came in real handy. What was happening was

wsEnd = Worksheets.Count

and

Do Until ws = wsEnd

was exiting the loop before te code ran on the last page. This would have been more obvious had I been running the code on the actual workbook instead of a test book that only had 2 sheets to loop through. Thanks for all the help.

Paul