VBA Question... Rename sheet by pasting

[twiss]

Hi everyone, its my first post so be gentle!

I'm sure someone knows the answer to this but I haven't really used a huge amount of excel vba

I have a list of items in a testing document (several hundred functions to be tested) and need to make a detail sheet for each function to be tested. So far I have come up with a macro that will create the new sheet, move it to the end of the workbook, create all of the headers for the sheet, and perform some calculations on the new sheet.

The issue I am having is with the renaming of the sheet. I'm setting it up so you click on the function name to be tested, then run the macro.

It uses selection.copy first to pick up the name I've clicked on, then uses Sheets.Add to create the new sheet, then I'm using activesheet.name to rename the sheet.
Specifically I'm using:

activesheet.name = selection.paste

It seems like the obvious way of doing it to me but it does nothing.
I have also tried:

activesheet.name = activesheet.paste

Instead of returning the copied data, this just returns the value TRUE, and that is what it names the sheet!

Can anyone shed any light on this for me?

Thanks,
Twiss

p.s.

here is all the code just in case

Selection.Copy
    Sheets.Add
    ActiveSheet.Name = ActiveSheet.Paste
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

    Dim ShName As String
    ShName = Selection.Value
    Sheets.Add.Name = ShName
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)

 

[VoG]

Try

s = ActiveCell.Value
Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = s

 

[baitmaster]

welcome to the board

selection.copy is copying the cell (or range of cells), not refering to the value within it (them). You don't need this step. What you want to do is refer to the .value of the selection

sheets.add
activesheet.name = selection.value

You'll get a problem if you have more than one cell selected, there's various ways to fix that if you need to, just ask...

 

[twiss]

Brilliant! Thanks for the speedy replies guys! I can see I have come to the right web site!!!

I will admit I just went straight for Andrew's solution and it worked perfectly

Now I am running the following to add " BR" on the end and its working perfectly.

Dim ShName As String
ShName = Selection.Value
    Sheets.Add.Name = ShName & " BR"
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)

I do have another question regarding creating hyperlinks using VBA... is it ok to ask here or would a new thread be best?

Thanks,
Twiss

 

[VoG]

New thread please.