VBA to make Sheet Name = Cell Value

[JDSOuth49]

Good afternoon,

I am tiring to rename a sheet tab based on a specific cell.

I have attempted to use this code:

Sub rename_Sheet()
Dim OldName As String
OldName = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = Range("A1").Value
On Error GoTo 0
If OldName = ActiveSheet.Name Then
MsgBox "Worksheet not renamed, Illegal name or no data available"
End If
End Sub

but it does not work for me.

I am pretty sure I am doing something incorrectly.

I am wanting to rename my sheet tab based on Cell "DQ7" with is a date.

Any help would be greatly appreciated.

 

[JDSOuth49]

Joe4,

I am still having no luck any more suggestions?

 

[Joe4]

Can you show me where in your code you added it?

And can you tell me exactly what you are doing that is not being automatically updated (i.e. walk me through an example of an update you are making that is not being reflected)?

If you click the F9 key, does it update the value as you expect?

 

[JDSOuth49]

Can you show me where in your code you added it?

And can you tell me exactly what you are doing that is not being automatically updated (i.e. walk me through an example of an update you are making that is not being reflected)?

If you click the F9 key, does it update the value as you expect?

Joe4,

I was able to figure it out... Thank you for your help.

 

[Joe4]

Joe4,

I was able to figure it out... Thank you for your help.

Excellent!

Note: Please do not mark a particular post as the solution unless that post actually contains the solution. If you want to post exactly what you did to fix it, then you can mark that post as containing the solution.

 

[JDSOuth49]

Excellent!

Note: Please do not mark a particular post as the solution unless that post actually contains the solution. If you want to post exactly what you did to fix it, then you can mark that post as containing the solution.

Honestly, I have another Issue now. All this stuff if confusing for me.

I changed the name of the file and now the Button does not work because it calls the old file name. I know that I can change the Workbook to register the new filename and then reference that new file name, but to no avail.

1. How do I set the VBA Project (Something.xlsm) to automatically update the the name of the file has changed?
1a. Where do I place the code?

2. In Module3: (contains this Macro - used for a button)

Sub Auto_Start_Run()
'
' Auto_Start_Run Macro
'

'
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet1.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet2.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet3.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet5.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet4.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet6.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet7.rename_Sheet"
End Sub

How do I make Module3 Macros Call the updated project name?

---Workbook.Active??

 

[Joe4]

Honestly, I have another Issue now. All this stuff if confusing for me.

I changed the name of the file and now the Button does not work because it calls the old file name. I know that I can change the Workbook to register the new filename and then reference that new file name, but to no avail.

1. How do I set the VBA Project (Something.xlsm) to automatically update the the name of the file has changed?
1a. Where do I place the code?

2. In Module3: (contains this Macro - used for a button)

Sub Auto_Start_Run()
'
' Auto_Start_Run Macro
'

'
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet1.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet2.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet3.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet5.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet4.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet6.rename_Sheet"
Application.Run "'Weekly Invoices & Receipts (R0).xlsm'!Sheet7.rename_Sheet"
End Sub

How do I make Module3 Macros Call the updated project name?

---Workbook.Active??

That is a new/different question altogether, and as such, should be posted in a new question thread.