Macro not working upon file name change

Hi,

I have an excel template that is macro enabled. However, when I save a new copy of the template under a different file name the macros are still linked to the original template name, is there a way to ensure that when a copy of the template is saved that the macros automatically update to that new file name and thus still work?

Many thanks,

Jaymie

I don't know if it possible but I have a template that people can use however have to save a new copy of the template to be able to use it. Ideally the macro that is in the template would be transferable to what ever the person changed the file name to.

For example in the attached image "eeeeeeeeeeee.xltm" would become the new file name, therefore enabling the new macro to work?

I hope this makes sense.

Jaymie

An image is not useful. Attach your file, or at a minimum paste in your code, and use CODE tags.

Sub tetetetsetetstes()
'
' tetetetsetetstes Macro
'

'
    Workbooks.Open Filename:= _
        "\\GBKDCPFS01\Profiles$\jrussell\Redirection\Desktop\fdfsdcvcvvvvvvvvv.xlsm"
    Windows("eeeeeeeeeeeeeeeeee.xltm").Activate
    Range("D51").Select
    Selection.Copy
    Windows("fdfsdcvcvvvvvvvvv.xlsm").Activate
    Range("D5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("eeeeeeeeeeeeeeeeee.xltm").Activate
    Range("D52").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("fdfsdcvcvvvvvvvvv.xlsm").Activate
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("eeeeeeeeeeeeeeeeee.xltm").Activate
    Range("G52").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("fdfsdcvcvvvvvvvvv.xlsm").Activate
    Range("G6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("eeeeeeeeeeeeeeeeee.xltm").Activate
    Range("M52").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("fdfsdcvcvvvvvvvvv.xlsm").Activate
    Range("G7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.Run "fdfsdcvcvvvvvvvvv.xlsm!CopySale"
    Sheets("New Sale").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

The correct way to use a template is to create a new Excel file using the template. Then you have a new file called Book1.xlsm that you can save. They should not be opening the template directly. If you want a file they can open directly then make a copy of, a different approach is required.

If this file is used as a true template, then the following changes would be required for the code and the file name doesn't matter.

Which worksheets in each of the workbooks do you want to work with? Explicitly identifying worksheets is much better than depending on what window is open. I have assumed that you are working with Sheet1 in both cases, but you will need to tailor that to your situation.

Sub tetetetsetetstes()
'
' tetetetsetetstes Macro
'

'
   Dim WB As Workbook
   
   Dim WSThis As Worksheet
   Dim WSOther As Worksheet
   
   Set WB = Workbooks.Open(Filename:= _
        "\\GBKDCPFS01\Profiles$\jrussell\Redirection\Desktop\fdfsdcvcvvvvvvvvv.xlsm")
        
   Set WSThis = ThisWorkbook.Worksheets("Sheet1")
   Set WSOther = WB.Worksheets("Sheet1")
   
   WSOther.Range("D5") = WSThis.Range("D51")
   WSOther.Range("D6") = WSThis.Range("D52")
   WSOther.Range("G6") = WSThis.Range("G52")
   WSOther.Range("G7") = WSThis.Range("DM52")
        
   Application.CutCopyMode = False
    
    Application.Run "fdfsdcvcvvvvvvvvv.xlsm!CopySale"
    Sheets("New Sale").Select
    ThisWorkbook.Save

End Sub