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

JDSOuth49

New Member
Joined
Feb 16, 2024
Messages
46
Office Version
  1. 365
Platform
  1. Windows
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??
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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.
 
Upvote 0
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:
1709391217291.png

Which matches this:
1709391343731.png


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)
1709391925787.png


Thank you in advance
 
Upvote 0
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.
 
Upvote 0
Solution
Glad I could help. Another approach when there are multiple lines doing the same thing in a nice order:
VBA Code:
Dim i As Integer
For i = 1 to 7
    Application.Run "'" & ThisWorkbook.Name & "'!Sheet" & i & ".rename_Sheet"
Next
 
Upvote 0
Glad I could help. Another approach when there are multiple lines doing the same thing in a nice order:
VBA Code:
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:
1710177876190.png

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
 
Upvote 0
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).
 
Upvote 0
This is what I have for sheets (1-7) Shown is sheet 7

VBA Code:
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

VBA Code:
Sub vba_activate_workbook()
    ThisWorkbook.Activate
End Sub

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top