Name a sheet a cell value

davulf

Active Member
Joined
Jul 4, 2005
Messages
273
Hey,

I was wondering how to take a value stored in a cell, and then rename the sheet to that value using VBA.

Algorithm:
1. Copy Sheet1 at end of book
2. Select Sheet1, cell A1
3. Copy cell A1
4. Rename Sheet1Copy to cell A1.value

Thanks,
DaVuLF
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
This perhaps.
Code:
With Worksheets("Sheet1")
     .Copy After:=Worksheets(Worksheets.Count)
     ActiveSheet.Name = .Range("A1")
End With
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Something like this
Code:
Sub NameMe()

    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Sheets(3)
    Sheets("Sheet1 (2)").Select
    Sheets("Sheet1 (2)").Name = Sheet1.Range("$A$1")
   
End Sub
lenze
 

davulf

Active Member
Joined
Jul 4, 2005
Messages
273
Yea, that's what I have so far. The thing is that A1 isn't the only name.

There is a list within the A1-A75 range, and I need a new worksheet for each. In the B1-B75 range I have e-mail addresses that I'm trying to send the sheet to.

Code:
    Dim sName As String
    Dim sEmail As String
    sName = Sheets("Name").Range("A1").Text
    sEmail = Sheets("Name").Range("B1").Text
    Sheets("BingoMock (2)").Name = sName
    Sheets(sName).Copy
    .SendMail Recipients:=sEmail, _
    Subject:="Keener Bingo " & sName & Format(Date, "dd/mm/yy")
    Sheets(sName).Delete

Thanks for the help guys.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

Hello davulf,
I have a pretty good idea of what you're looking to do but could use a better explanation
in english as opposed to just the sample code.
As it is you wouldn't be able to loop through the A1:A75 range for this because the
sheet named 'BingoMock (2)' won't be there after the first time around.
(And I don't see where it got copied in the first place so I'm left to wonder, is BingoMock one
of the names in the A1:A75 list?)

If you can explain in words what all you're wanting this to do I'm sure we can show you how.
 

davulf

Active Member
Joined
Jul 4, 2005
Messages
273
Hey Ace, I'd be happy to explain.

Essentially I'm looking for a for loop. For each value within the A1-A75 range, I need to copy the sheet, rename it, email it, delete it, and repeat.

More or less I'm just having trouble looping through the ranges and storing the increment that I'm currently at. I'm thining something like.

Code:
for i=1;i<76;i++
Range("A"& i).Select
...

I'm finding it harder to describe in words than in code, because I'm really thinking of it from a coding perspective. I can think of how to write this in other languages, but not VBA which I'm not really all that familiar with.

Thanks for any and all help you can provide.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Don't select.:)
Code:
With Worksheets("Sheet1")
     For Each c In .Range("A1:A75")
          .Copy After:=Worksheets(Worksheets.Count)
          ActiveSheet.Name = c.Value
      Next c
End With
 

davulf

Active Member
Joined
Jul 4, 2005
Messages
273
Nice Norie, I like that. Good stuff.

One thing though, how would I tell it to e-mail to the corresponding value in column b. (ie, B1-B75)
 

davulf

Active Member
Joined
Jul 4, 2005
Messages
273
Here is what I'm using currently:

Code:
Sub CopyStuff()

Dim sName As String
Dim sEmail As String

With Worksheets("Name")
     For Each c In .Range("A1:A75")
          Sheets("BingoMock").Copy After:=Worksheets(Worksheets.Count)
          ActiveSheet.Name = c.Value
          Sheets("BingoMock").Select
          ActiveCell.Range("A1:E5").Select
          Selection.Copy
          Sheets(c.Value).Select
          Sheets(c.Value).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
          sName = c.Value
          sEmail = c.Offset(0, 1).Value
          Sheets(sName).Copy
          .SendMail Recipients:=sEmail, _
        Subject:="Keener Bingo " & sName & Format(Date, "dd/mm/yy")
          Sheets(sName).Select
          ActiveWindow.SelectedSheets.Delete
      Next c
End With

End Sub

This is bringing up a Debug error to do with the .SendMail. I'm not sure what is wrong with it. Additionally, I'm seeing some problems with the copying for some reason. Any idea?

Thanks,
DaVuLF
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
DaVuLF

SendMail is not a Worksheet method, it only applies to the Workbook object.

Also in the posted code you are using Select, that's not usually needed.

I'm just off now but I'll try and take a good look at the code tomorrow.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,041
Messages
5,545,679
Members
410,697
Latest member
srishtijain0708
Top