Hi Marcus,

A sample spreadsheet would be most helpful. Here is my email:
(remove sample from address)

I think we are close to nailing this Marcus.

Thank you

"marcus" wrote:


Hi Gootroots

Strange that it does not work for you, in my mochup it works
perfectly. Maybe I'll send you the spreadsheet and you will see it
working OK.

lr = sh.Cells(Rows.Count, 2).End(xlUp).Row

The above line gets the integer value of the last used row in Column
B. So if you enter your new unique identifyer at the bottom of
column, B before the sort, it will assign the row number to the unique
identifyer entered. If say you entered the unique value in cell B8001
then LR would = 8001. You do this to get the used range in Column B
from B2 to B LR (being 8001). This would equal Sel in the next part
of the procedure.

rng.Sort Range(sel), xlAscending
the sort needs to take in the whole sheet and sort on €œB€�

I was not aware you wanted to sort the whole sheet I thought it was
just unique data in Col B of a sheet. Change the range (RNG) to equal
something like this

Set rng = sh.Range("A10:X" & lr)

Above X would be the last Column with Data in it, change to suit.
This will ensure that your whole range is sorted. The SEL range
explained above would be used as the criteria.

rng.Sort Range(sel), xlAscending

is sorting column B in Alpa ascending order. The range SEL is
assigned to B2 to B Last used row or B2:B8001 in the example above.

I presume 5 means the first 5 characters from the left of the sheet name,
these sheets were not processed

Yes this is correct 5 does cover the first 5 characters in the word
"Usual". These sheets were processed perfectly on my machine. Send
me your email address and I will post you a sample spreadsheet.

Take care and good luck.

Marcus
.