Need help Editing Ron de Bruin Script

[kris.king]

Hello all,

I have been trying to sort data from one worksheet onto others and I
read many newsgroup postings and alot of them recommended Ron de
Bruin's scripts. So I took a look and found this one would do what I
needed:
http://www.rondebruin.nl/copy5.htm#all
It was fast and easy to use and configure. However when you run the
macro it auto names the sheets based on the cell value. But I need to
name the sheet something different.

My values are:
Spring '06
Summer '06
Fall '06
Winter '07

And I need my sheet names to be increased by 1. For example all rows
with the spring '06 value will be stored in a sheet named Spring '07.
Now this may seem strange but customers that purchase our product in
spring '06 have right of first refusal for Spring '07 so I must keep
track. My VBA skills are minimal at best (I could change the values
where the script told me and that was about it.) I tried using an
if-then-elseif statement for WSNew.Name area of the script but I kept
on recieving the error that is built into the script right there.
Anyone have any advice or sudgestions or another command to try?

I would really appreciate the help.

Kris K.

PS Also if anyone knows how to strip the formating from the text that
has been copied by the script into new worksheets I could use help with
that as well

 

[Ron de Bruin]

Hi Kris K

I will look at it this evening
Have every Unique value two numeric characters at the end ?

 

[Kris]

Yes, they all have two numeric characters at the end and also an
apostrophe " ' " but that isn't important and can be removed it if
makes coding this any easier.

Thanks for the help Ron I know many of us depend on you for your
talents.

 

[Ron de Bruin]

hi Kris

Test this

WSNew.Name = Left(cell.Value, Len(cell.Value) - 2) & Format(Val(Right(cell.Value, 2)) + 1, "00")

 

[Kris]

That worked great thanks.

 

[Ron de Bruin]

Hi Kris

Forgot this

PS Also if anyone knows how to strip the formating from the text that
has been copied by the script into new worksheets I could use help with
that as well

Add WSNew.Cells.ClearFormats below WSNew.Columns.AutoFit

WSNew.Columns.AutoFit
WSNew.Cells.ClearFormats