Code troubleshooting

PaulM2009

Board Regular
Joined
Feb 25, 2009
Messages
51
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.

Code:
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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Joined
Jul 30, 2006
Messages
3,656
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

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Feb 25, 2009
Messages
51
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

Board Regular
Joined
Feb 25, 2009
Messages
51

ADVERTISEMENT

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

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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

Board Regular
Joined
Feb 25, 2009
Messages
51
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top