VBA Question... Rename sheet by pasting

twiss

New Member
Joined
Jun 27, 2012
Messages
4
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:

Code:
activesheet.name = selection.paste

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

Code:
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

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

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

Try:

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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

New Member
Joined
Jun 27, 2012
Messages
4
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,384
Messages
5,601,326
Members
414,443
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top