Run the macro even if the last 3 letters is renamed

[deejay29]

Good day,

May I ask if it is possible to run the macro even if some letters or characters on the title of the workbook I am working with is renamed?

this is the code I am working with:

Sub WBS()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Workbooks("Total cost.xlsm").Worksheets(3).Range("A3:A300")
    Set targetColumn = Workbooks("backing sheet (Jan).xlsm").Worksheets(2).Range("D6:D300")

    sourceColumn.Copy
    targetColumn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                          SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Call Resource_Name
End Sub

what I really want to do is run the macro even if the name of the workbook is changed. as you can see the value I am copying is pasted on "backing sheet (Jan).xlsm" I want to change the name to "backing sheet (Feb).xlsm" and I know it wont run because the target workbook does not exist technically.

is there any possible way to deal with this?

I tried to do this

Sub WBS()

    Dim monthAbbr As String
    monthAbbr = Range("monthAbbr").Value

    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Workbooks("Total cost.xlsm").Worksheets(3).Range("A3:A300")
    Set targetColumn = Workbooks("backing sheet " & monthAbbr & ".xlsm").Worksheets(2).Range("D6:D300")

    sourceColumn.Copy
    targetColumn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                          SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Call Resource_Name
End Sub

but it gives me an error on
monthAbbr = Range("monthAbbr").Value

 

[deejay29]

I've solved this. thanks.