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

 

[davulf]

Thanks Norie. I do want it to be a new workbook that is e-mail, just for clarity sake.

 

[HalfAce]

Thanks Norie. I do want it to be a new workbook that is e-mail, just for clarity sake.

So... if we looped through the range as Norie has done, you want to save each new sheet
as a separate (one sheet) workbook and then email that to the address in column B of the
same row the sheet's new name came from?
(And then I presume kill the new workbook(s) after they get sent off?)

That sound anywhere near right?

 

[davulf]

That, my friend, is exactly what I'm after.

 

[HalfAce]

Something like this perhaps?

Sub EmailTheList()
Dim ThsRw&
For ThsRw = 1 To 75
  If Not IsEmpty(Cells(ThsRw, "A")) And _
    Not IsEmpty(Cells(ThsRw, "B")) Then
      ActiveSheet.Copy
      ActiveSheet.Name = Cells(ThsRw, "A").Value
      ActiveWorkbook.SendMail _
        Cells(ThsRw, "B").Value, _
        "Keener Bingo " & Cells(ThsRw, "A").Value & " " & Format(Date, "dd/mm/yy")
      ActiveWorkbook.Close False
  End If
Next ThsRw
End Sub

 

[davulf]

So this says that you will copy the sheet solely to a new workbook (so as not to overload the original), and send it via e-mail?

I think that makes sense, but this is some pretty advanced VBA code that I don't entirely understand. I just want to make sure that the values are being copied and not the formulas. If you just copy the sheet, then the formulas stay instead. As they are linked to other cells, this doesn't work out.

Is this easy to fix?

 

[davulf]

Okay, I'm trying to understand this code, but I don't see any absolute references, so how does it know which sheet to use, where to select, what the copy, that type of thing.

Granted, you know much more than I do about this type of thing so I am probably entirely wrong, but I just want to clear these things up.

 

[HalfAce]

It's always nice to see someone wanting to learn how the code is working instead of
just wanting something that does the job for them.

how does it know which sheet to use, where to select, what the copy, that type of thing

It just copies the entire active sheet. You seldom ever have to actually select
an object to work with it. (even to copy it)
Once it's copied, it goes into a new workbook, and then that becomes the active
workbook / worksheet.

I made a slight change to make sure all formulas are converted to fixed values.
I then commented each line of code as to what it's doing.
(It'll be easier to read once you paste it into a module.)
Don't hesitate to ask more questions if there's something that's not clear. :wink:

Sub EmailTheList()

'declare the variable 'ThsRw' as Long
Dim ThsRw&

'begin loop for 75 iterations
For ThsRw = 1 To 75

  'test for current row of loop, columns A & B not being blank
  If Not IsEmpty(Cells(ThsRw, "A")) And _
    Not IsEmpty(Cells(ThsRw, "B")) Then
            
      'copy the entire sheet (to a new workbook)
      ActiveSheet.Copy
      
      'name the sheet in new workbook the value in column A of the row being looped
      ActiveSheet.Name = Cells(ThsRw, "A").Value
      
      'convert any formulas in the new sheet to fixed values
      ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
    
     'send the active workbook (which is the new workbook at this point) _
      as email attachment to the address in column B of the looped row _
      and "Keener Bingo", the column A value and the date in the subject line
      ActiveWorkbook.SendMail _
      Cells(ThsRw, "B").Value, _
      "Keener Bingo " & Cells(ThsRw, "A").Value & " " & Format(Date, "dd/mm/yy")
      
     'close the new workbook without saving it
     ActiveWorkbook.Close False
      
  'end the test for current row of loop, columns A & B not being blank
  End If
  
'move down to the next row and start all over again
Next ThsRw

End Sub

That help clear it up any?

[EDIT:]
You can test this without sending out a bunch of emails until you're comfortable with
how it works by simply commenting out the two lines:
ActiveWorkbook.SendMail
and
ActiveWorkbook.Close False

It never hurts to be sure before sending that many emails.