Renaming Sheets

Evar sense I went tu thu Evlin Wood Skool of spede reddin, I two ken red.
Now I just need to count!

I have several worksheets to create from an existing worksheet, that's simple enough. What I am stumbling on is renaming them in a specific format.

Sheet(1).Name = "CAL 001", this is the sheet I am copying.
Once copied, at least one time, the new sheet name is "CAL 001(1)" what I need is "CAL 002".

I am trying to do this in a macro because I have many workbooks with many sheets with a similar naming convention. IE: CAL 001, ASY 001, GWI 001 etc.
The toughest part is I must retain the zeros and must retain the three character alpha three character numeric format with the space between alpha and numeric values, IE, "CAL 001".
Any help creating/editing code to rename my sheets is appreciated.
Current code is below, with attempted renaming including counting.

Please Login or Register  to view this content.

Excellent!
I don't fully understand some of the code you used,but I sure am going to read about it.
a = Split(Worksheets(1).Name)

I never heard of "Split"

and
ActiveSheet.Name = a(0) & " " & Format(Worksheets.Count, "00#")

I'm not even going there!

Thanks! A lot!