Need help Editing Ron de Bruin Script

[kris.king@gmail.com]

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 ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


<kris.king@gmail.com> wrote in message news:1141149691.052428.320740@t39g2000cwt.googlegroups.com...
> 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
>

[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")



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Kris" <kris.king@gmail.com> wrote in message news:1141151804.094743.146150@t39g2000cwt.googlegroups.com...
> 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.
>

[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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Kris" <kris.king@gmail.com> wrote in message news:1141153935.448897.138550@v46g2000cwv.googlegroups.com...
> That worked great thanks.
>