Setting worksheet name equal to variable value

[craigwg]

I have a nice simple little module set up, but I can't figure out how to rename a worksheet to the value of a cell on another worksheet. Here is what I have:

[VBA]
Dim Worksheet As Worksheet
Dim ServerName
Range("A1").Select
Set ServerName = ActiveCell
Selection.Cut
Sheets.Add
Worksheet.Name = ServerName.Value ''this line is the problem
Sheets("Sheet1").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub[/VBA]

I've tried a few things but can't figure this out for some reason. McHelp!

Craig

[lucas]

When you add a sheet, the new sheet becomes active so "activecell" is some cell on the new sheet which has nothing in it.

You will have to qualify Servername with the actual name of the sheet that contains the cell that you want to get the name from.

ied something like this:

[VBA]Set ServerName = ActiveWorkbook.Sheets("Sheet2").Range("A1").Value[/VBA]

[craigwg]

Good try, Lucas, but Loading the variable is not an issue. That part is working smooth. I understand what you mean about the changing activecell, but I have accounted for that. I want to start on sheet1, which has the values, and get the value of Cell A1, then add the new sheet, set the new sheet to what was present on A1 of the first sheet, then go back to the first sheet and delete Row 1. I will build this to loop through the rows later.

Hope that explains my goal. Here is my revised code. I think it's a little cleaner. I also changed the variable name. I think using "ServerName" might confuse others.

Dim Worksheet As Worksheet
    Dim MickeyMouse
    Range("A1").Select
    Set MickeyMouse = ActiveCell
    Sheets.Add
    Worksheet.Name = MickeyMouse --This Line is the only part not working
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
End Sub

That said, I still need help!

[lucas]

Ok....maybe:

[VBA]Dim Worksheet As Worksheet
Dim MickeyMouse
Range("A1").Select
Set MickeyMouse = ActiveCell
Sheets.Add
ActiveSheet.Name = MickeyMouse
Sheets("Sheet1").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
[/VBA]

[craigwg]

I have tried that along with a host of other things. No luck though. Is it possible to set the name of a worksheet equal to the value of a variable? I guess that is my ultimate question.

Craig

[lucas]

It works for me. See attached.

[craigwg]

Sweet mother of pearl!! Why on earth did mine not work? Wierd. When I loaded yours and ran it it errored out and pointed to my sheet. I restarted Excel and yours worked. I'm happy but confused. Thanks for working with me!

[lucas]

Craig, please mark your thread solved using the thread tools at the top of the page if you have all you need on this.

That will keep people looking to help from reading and entire thread that has been solved...

[geekgirlau]

Hi Craig,

Another option. This loops through the cells on Sheet1 starting from A1, creates the new sheets after the current sheet, then clears the range containing the sheet names. No selecting required!

[vba]
Sub AddSheets()
Dim Worksheet As Worksheet
Dim rng As Range


For Each rng In Sheet1.Range("A1").CurrentRegion
Sheets.Add , Sheets(Sheets.Count)
ActiveSheet.Name = rng
Next rng

Sheet1.Range("A1").CurrentRegion.Clear
End Sub
[/vba]

Of course you might want some error handling here in case a sheet already exists with that name. Also instead of "CurrentRegion" you could have a named range as CurrentRegion will select all adjoining cells that have a value.