Loop through sheets in a workbook

[Mumps1]

I have the following code that assigns cell values to an array. Then it searches for the array values on another sheet and if it finds them, it copies the value of two cells to that sheet. The code works well on the selected sheet, one sheet at a time. I have 27 sheets in the workbook and I would like to run the code on all the sheets except the sheets named "1Summary", "2Blank" and "SheetNames" (This last sheet is hidden.) I'm having trouble finding the loop to make it work properly. In the sample file I posted, if we use the sheet named "AD-HIGHLAND", I would like to search column A for Last Name and column B for First Name. In this case it would find "Balkaran" and "Anita". Then I would like to find "Balkaran" and "Anita" in the sheet "1Summary" and from "AD-HIGHLAND", copy G17 to D7 in sheet "1Summary" and H17 to E7 in sheet "1Summary". It should then continue to search for any more names in Columns A and B in "AD-HIGHLAND". It would find "Kitney" in column A and "Sean" in column B and from "AD-HIGHLAND", copy G33 to D28 in sheet "1Summary" and H33 to E28 in sheet "1Summary". This process would continue until there are no more names in columns A and B. Then it would repeat the whole process for the remaining sheets with the exception of the three sheets "1Summary", "2Blank" and "SheetNames". Any suggestions would be greatly appreciated.

Please Login or Register  to view this content.

This is a link to another post:
http://www.ozgrid.com/forum/showthre...472#post631472

[rylo]

Hi

Try this as the outer loop

Please Login or Register  to view this content.

You could either make this a main sub and have it call your existing sub, or make sure you put

Please Login or Register  to view this content.

as the first line of the inner processing.

rylo

[Mumps1]

Hello rylo. Thank you so much for your quick response. I tried your suggestion and it worked for the first sheet only without looping to the remaining sheets. I believe that this happens because the line:

Please Login or Register  to view this content.

exits the sub when the variable x is empty. It becomes empty when a name is not found in the active sheet. If I remove this line of code, I get a 'Subscript out of range' error on this line:

Please Login or Register  to view this content.

I believe that this happens because 'MyArray(5)' and 'MyArray(6)' are empty at this point. Somehow, I need to have the code go the next sheet after all the names are found and x and 'MyArray(5)' and 'MyArray(6)' become empty. I can't figure out a solution to this problem.

[rylo]

Hi

I put the following and your code in a general module in the example workbook, and it seemed to run and bring back results. Where is it not working correctly?

Please Login or Register  to view this content.

rylo

[Mumps1]

Hi rylo. When I try it, it works for the first sheet only. If I disable the line:

Please Login or Register  to view this content.

I get a 'Subscript out of range' error. If I change the above line of code to

Please Login or Register  to view this content.

it puts zeros ($0.00) in all the cells in columns D and E in Sheets("1Summary").

[rylo]

Hi

Are you running it on the 2 example files?

rylo

[Mumps1]

Hello rylo. I am running it on the file I attached to my original posting.

[Mumps1]

I have tried a different approach and it works.