Renaming worksheet tabs

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi
I dont know if anyone can help me, but I am having problems
renaming the worksheet tabs on a spreadsheet.

I have set up a procedure that when run, opens a dialog box
and asks the user to input a name into a text box.
This name is then copied and pasted to a cell on a worksheet.

I then take a copy of an existing worksheet, and the
intention is to get Excel to automatically rename the new
copy with the name entered in the text box and pasted to
the sheet (if you see what I mean).

I am using the following code to try this last part:

Sheets("WORKSHEET").Select
Range("A1").Select -----> cell holding the name
Selection.Copy
Sheets("NEWWORKSHEET").Select ---->new copy of the sheet
Sheets("NEWWORKSHEET").Name = ???????

It is the ????? I cannot seem to do. If I record a macro
of me copying the name and pasting it, the code comes out
with the actual text, not a generic "whatever is on the
clipboard".

Is there a simple way of adding this to the code, so that
whatever is entered in cell A1 can be used to rename the
worksheet??

Thanks in advance for your help

Dave
 
Dave-

Here's the code I'm using to do something similar, and
it does work:

Sheets("Worksheet").Activate
NewSheetName = Range("A1")
Worksheets("Worksheet").Copy _
After:=Sheets(Sheets.Count) 'this puts the new
sheet after all current ones
ActiveSheet.Name = NewSheetName

Hope that helps,
Marty
 
Hi,
Assuming they are in the same workbook, you could just try:

Sheets("NEWWORKSHEET").Name = Sheets("WORKSHEET").Range
("A1").value

Regards,
Mark
 
Back
Top