Or more likely set up the prefix with an InputBox

sBase = Application.InputBox("Supply Prefix for worksheet renaming", _
"Rename worksheets", "USA")
If sBase = "" Then
MsgBox "Cancelled by your command"
exit sub
end if

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Bob Phillips" wrote in message ...
Mike,

This will get it from A1 on Sheet1, change to suit

Dim sBase as string
Dim i as long
Dim sh As Object

sBase = Worksheets("Sheet1").Range("A1").Value
For Each sh In Activeworkbook.Sheets
i = i + 1
sh.name = sBase & i
Next sh



--

HTH

RP
(remove nothere from the email address if mailing direct)


"MikeM" <michael[dot]mittelmanl[at]db[dot]com wrote in message
...
Bob:
Thanks so much for your quick reply! I tried it and it works perfectly.

Could it be set up so I could define the worksheet name before running the
macro? (by typing it into a cell or something like that)?

For example, one workbook might need to be USA1, USA2, etc. and another
might need to be CANADA1, CANADA2, etc.

"Bob Phillips" wrote:

Const sBase as string = "USA"
Dim i as long
Dim sh As Object

For Each sh In Activeworkbook.Sheets
i = i + 1
sh.name = sBase & i
Next sh

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MikeM" <michael[dot]mittelmanl[at]db[dot]com wrote in message
...
I would like to take an existing workbook and rename all the worksheet
tabs
at one time. For example, I might have ten worksheets with various
names
and
I'd like them all to be named USA1, USA2, USA3 and so on.

Can this be easily done with some VBA code? (I've seen some similar
questions, but none exactly like this one.)

Thanks.