Excel VBA - Not looping through my while loop

[ZeDoctor]

So I have a code thats pasting data into an excel column, i'm then trying to loop through the data in that column and create a new sheet from data for every value in column B but it's stopping after completing this action once and not looping through the column.

Any ideas?

Please Login or Register  to view this content.

[rorya]

Specify a sheet for the Cells(i, 2) property call.

[6StringJazzer]

What module is this code in? What worksheet are you looping through?

At the end of the loop you create a new sheet, which becomes the active sheet. If this code is in a standard module (like Module1) then the condition at the top of the loop will refer to Cells(i, 2) in the active sheet, which is a different sheet than it referred to the first time through the loop. Because the sheet was just created, there is no data in column 2 and your loop will exit.

It is a best practice to qualify range references, such as what you did in the first line of the loop.

It is not a good practice to select thing if you are not doing so for the convenience of the user. I would rewrite the code as follows to clean up, but this doesn't solve your original question:

Please Login or Register  to view this content.

[ZeDoctor]

Perfect, worked a charm, thanks guys.

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.