Name a sheet a cell value

[davulf]

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

 

[Norie]

This perhaps.

With Worksheets("Sheet1")
     .Copy After:=Worksheets(Worksheets.Count)
     ActiveSheet.Name = .Range("A1")
End With

 

[lenze]

Something like this

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]

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.

    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]

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]

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.

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]

Don't select.

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

 

[davulf]

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]

Here is what I'm using currently:

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]

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.