Sort worksheets based on a cell value in each worksheet.

[Patrick]

Can this be done? I have 850ish worksheets in a workbook that I would like
to sort by geographic region - and a cell contains the region value on each
worksheet. The region value is alpha, btw. I've checked the existing posts,
and this doesn't seem to be covered, and I have not been able to figure it
out.

Thanks you!

 

[Pete_UK]

Do you mean that you want to re-position the worksheets in the
workbook, based on the region value in each sheet? Can the sheets be
renamed to this region value?

Pete

 

[Patrick]

Yes, I want to re-position the sheets in the book based upon the value of a
cell in each sheet.

I would prefer not to rename the sheets because they are currently named
after the account which they reference. Maybe some hybrid NE-1001 for an
account named 1001 that is in the region NE. And then sort the sheets by
alpha via the readily accessible solution? Is this possible? I'm open
to any workable programatic solution - I can't manually sort 850 sheets many
times a month.

Thank you!

 

[Pete_UK]

You will need a macro to do this. I think a workable solution would be
along the following lines:

Create a new sheet.
Scan through all other worksheets, recording the name of the sheet and
the region code in the range A1 to B850 of the new sheet.
Sort A1:B850 using region code as the sort key.
Scan through this range in sequence to obtain the worksheet name, and
move the sheet with this name to its appropriate position.

Hope this helps to get you started.

Pete

 

[Pete_UK]

Forgot to add:

Delete the new sheet at the end.

Pete