Changing Name of worksheet or preferably workbook.

[Diesel24]

Through VBA I am looking at changing the name of my workbook that I will send out to people, then having it sent back to me through a control button. The sending part is fine, but not the naming.... I got that from Microsoft (I know it is for worksheet, but I would prefer the the workbook name change). I always get hung up with a run-time error "1004" on rs.Name = rs.Range("C4") Code below. I have used other code and typically does the same thing.Can anyone help me out in what the issue is?

Also is SendWorkBook in the right place to continuing the two steps at once?

Thank you....

Sub RenameSheet()


Dim rs As Worksheet


For Each rs In Sheets
rs.Name = rs.Range("C4")
Next rs
SendWorkBook
End Sub




Sub SendWorkBook()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next
With OutlookMail
.To = "dl@abc.com"
.CC = ""
.BCC = ""
.Subject = "Survey"
.Body = "Return of Survey"
.Attachments.Add Application.ActiveWorkbook.FullName
.Send
End With
Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

 

[Meesam_ali]

Your workbook should be saved before sent else the information (change of sheet names) is lost. I have modified the code and final is as below.

Sub RenameSheet()


Dim rs As Worksheet
Dim wb As Workbook
Set wb = Application.ActiveWorkbook


For Each rs In Sheets
    rs.Name = rs.Range("C4")
Next rs
wb.Save


Call SendWorkBook
End Sub




Sub SendWorkBook()
Dim OutlookApp As Object
Dim OutlookMail As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
On Error Resume Next


With OutlookMail
    .To = "dl@abc.com"
    .CC = ""
    .BCC = ""
    .Subject = "Survey"
    .Body = "Return of Survey"
    .Attachments.Add Application.ActiveWorkbook.FullName
    .display
    '.Send
End With


Set OutlookMail = Nothing
Set OutlookApp = Nothing
End Sub

 

[Diesel24]

Thank you - I will check this out.

 

[Diesel24]

Still getting hung up on:

rs.Name = rs.Range("C4")

1004 error

 

[Meesam_ali]

Are you sure C4 is having correct name i.e. not some characters which may not be allowed as sheet names or too long a sheet name? Also instead of using "activeworkbook" try using "thisworkbook". The code should work fine. Else you can upload your book on googledrive and share for me to have a look and solve the issue.

 

[Diesel24]

Thank you - I will try that option.