use a collection..

when your workbook opens, create a collection

in the collection you can have a key and an item.

Cycle through ALL worksheets

On each worksheet get the value of E13 and the name of the worksheet.

when you add an item to a collection, make what was in E13 the key, and the
worksheet name the item.

then you don't have to cycle through each worksheet for every sheet.. simply
reference the key item of the collection.

If that's a lot, then build a list of PO-numbers and their respective
worksheet names and put them on another worksheet tab.




"Nigel" wrote:

Hi again,

i thought i should mention that the number of PO's is not always 1000. they
are generated when required starting at the begining with 1 and so forth.
i have tried for i = 1 to 1000 but nothing seems to be coming to mind

regs,

NS


"Nigel" wrote:

Hi,

i have been really struggling with this macro. i have a PO Number.
example : 4533211/NICYC

in my po book i have up to 1000 purchase orders/ each with a unigue number.
i have set up a form and this number is set under a variable called PONumber
on every PO, the number is found in cell E13

what i need to do is this.
1.open my form and enter my PO number to find
2.press apply and the macro should take the number, and look through all of
the PO's until it finds the matching number.
3. when the number is found, stop searching and make this sheet active.

i have everything else completed ie errors etc, i just cannot get this right.

please can someone help me?

regs,

NS