VBA Project (Automatic Update) and Macro reference with file name change

[JDSOuth49]

Experts...

I am needing some help (Please see below) "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??

 

[Micron]

I'm not sure I understand what you've posted and are asking. This might be answering 1 and 2 but the last question is quite vague.
If you want code in a wb to still work when you run that code and it refers to the filename of the original wb but you have changed the file name then use a relative reference. Instead of "Weekly Invoices & Receipts (RO).xlsm
perhaps use
ThisWorkbook.Name

as long as you don't need the folder path to the workbook as well.

 

[JDSOuth49]

I'm not sure I understand what you've posted and are asking. This might be answering 1 and 2 but the last question is quite vague.
If you want code in a wb to still work when you run that code and it refers to the filename of the original wb but you have changed the file name then use a relative reference. Instead of "Weekly Invoices & Receipts (RO).xlsm
perhaps use
ThisWorkbook.Name

as long as you don't need the folder path to the workbook as well.

Thank you for the reply.

So my original file name/Vba Project is this:

Which matches this:

I CHANGED THE FILE NAME/VBA PROJECT NAME TO: 03-03_03-09-24_Weekly Time & Invoice.xlsm
So my question is, how do I get the (Changed VBA Project Name) in
Sub Auto_Start_Run()
Application.Run "'?????????????????????????'!Sheet1.rename_Sheet" to change? (Every time the excel files' name is changed) without having to enter it manually)

Thank you in advance

 

[Micron]

So you are copying a workbook with all of its code, but the code references the workbook by whatever name it had before the copy?
Then the code you want to run is in the copied workbook and you are not trying to run code that's in the original workbook? Then I would try using ThisWorkBook.Name as I suggested. Maybe try

Application.Run "'" & ThisWorkbook.Name & "'!Sheet1.rename_Sheet

Then it should not matter what the workbook name is as long as the code you're trying to run is in the same workbook.

 

[JDSOuth49]

Application.Run "'" & ThisWorkbook.Name & "'!Sheet1.rename_Sheet

Thank you very much... It works great

 

[Micron]

Glad I could help. Another approach when there are multiple lines doing the same thing in a nice order:

Dim i As Integer
For i = 1 to 7
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet" & i & ".rename_Sheet"
Next

 

[JDSOuth49]

Glad I could help. Another approach when there are multiple lines doing the same thing in a nice order:

Dim i As Integer
For i = 1 to 7
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet" & i & ".rename_Sheet"
Next

Micron,

I attempted to use you suggestion but I get This Error:

I probably messed something up this this my code: (I also put code in the wrong place and it messes things up)

Sub vba_activate_workbook()
ThisWorkbook.Activate
End Sub
Sub Auto_Start_New_Run()
'
' Auto_Start_Run Macro
'

'

Dim i As Integer
For i = 1 To 7
Application.Run "'" & ThisWorkbook.Name & "'!Sheet" & i & ".rename_Sheet"
Next


Application.Run "'" & ThisWorkbook.Name & "'!Sheet1.rename_Sheet"
Application.Run "'" & ThisWorkbook.Name & "'!Sheet2.rename_Sheet"
Application.Run "'" & ThisWorkbook.Name & "'!Sheet3.rename_Sheet"
Application.Run "'" & ThisWorkbook.Name & "'!Sheet4.rename_Sheet"
Application.Run "'" & ThisWorkbook.Name & "'!Sheet5.rename_Sheet"
Application.Run "'" & ThisWorkbook.Name & "'!Sheet6.rename_Sheet"
Application.Run "'" & ThisWorkbook.Name & "'!Sheet7.rename_Sheet"

Application.Goto Worksheets(1).Range("A1")

End Sub

Thank you in advance...Jason

 

[Micron]

Your sub name has the word "New" in it. In the message I don't see New as part of the name. Which one of those subs is responsible for raising the error? I'm guessing it is the called procedure rename_Sheet.

Please paste your copied code within code tags (use vba button on posting toolbar).

 

[JDSOuth49]

This is what I have for sheets (1-7) Shown is sheet 7

Sub rename_Sheet()

   ' This line of code activates the worksheet named "Sheet7".
    Worksheets(7).Activate
    

    Dim OldName As String
    Dim NewName As String
    
    OldName = ActiveSheet.Name
    NewName = Format(Range("l17"), "dd-mmm")
    
If OldName = NewName Then
        MsgBox "The date is the same"
        Exit Sub
    End If
    
    On Error Resume Next
        Application.DisplayAlerts = False
            ActiveSheet.Name = NewName
        Application.DisplayAlerts = True
        ActiveSheet.Name = NewName
    On Error GoTo 0

End Sub

Code in Module 1

Sub vba_activate_workbook()
    ThisWorkbook.Activate
End Sub

Sub Auto_Start_New_Run()
'
' Auto_Start_Run Macro
'

'

Dim i As Integer
For i = 1 To 7
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet" & i & ".rename_Sheet"
Next


    Application.Run "'" & ThisWorkbook.Name & "'!Sheet1.rename_Sheet"
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet2.rename_Sheet"
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet3.rename_Sheet"
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet4.rename_Sheet"
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet5.rename_Sheet"
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet6.rename_Sheet"
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet7.rename_Sheet"

    Application.Goto Worksheets(1).Range("A1")

End Sub

Essentially what is supposed to happen is this:

1. The Workbook will recognize that the Excel file Name changed and Update it automatically.
2. There is a button that is pressed on sheet 1 that will update the sheet tabs (Change the Date)
3. The Macros for the button should reference Auto_Start_New_Run