Okay, thank you!

"Bernie Deitrick" wrote:

Elise,

Is there something in the code below that brings up a box to connect to a link? I'm so
confused because now it's bringing up a dialog box that tells the macro to
draw a number from another workbook. It's never done this before...any ideas?

It should just ask for the new number - as a string, no link. Worked for me each time.

Also, what does the "Dim" mean?

Dim is a dimensioning statement, which tells the VBA what type of variable you want to use. In this
case, it is saying that myName should be a string, which prevents some autoconversion from happening
when you type a number into the inputbox.

HTH,
Bernie
MS Excel MVP



"Bernie Deitrick" wrote:

Elise,

That is a problem with the codename of the sheet becoming too long.

Try

Dim myName As String
Dim mySht As Worksheet

myName = InputBox("Enter the number of the new distributor.")
Sheets("201").Copy After:=Sheets(Sheets.Count)
Set mySht = Sheets(Sheets.Count)
mySht.Name = myName

mySht.Parent.VBProject.VBComponents(mySht.CodeName ) _
..Properties("_CodeName") = "sht" & Application.Proper(Replace(myName, " ", ""))


This will require a reference to the MS VBA extensibility.

HTH,
Bernie
MS Excel MVP


"Elise148" wrote in message
...
I posted something on here yesterday, and I got very good responses. However,
after I implemented each suggestion into VBA the macro worked only about 10
times. After that it came up with an error that said the worksheet could not
be copied...or something like that. This is what my code for that section
looks like...

This is at the beginning of my macro...what I need to do is copy a
worksheet. That's all I need to do...and it won't work...ANY SUGGESTIONS???


Sheets("201").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = InputBox("Enter the number of the new
distributor.")

Thanks,
Elise