Excel Excel VBA Challenge

[loftis01]

Having some challenges with the following situation.

Two files A & B

Macro is assigned with file A. (purpose of the marco is to copy information from file B and past it into file A).

The Issue: File A name changes. Example ( 06-06.xls to 06-07.xls) The macro is unable to find the new file name once the information has been copied from file B as it needs to go back to the originating file to paste the information extracted.

Help!

 

[loftis01]

Additional info: Test file A = Collected Balance Beta Test File.xlsm
Macro below - the name will be changed to a date mm-dd.xlsm

Sub Openfilewithdate()
'
' Openfilewithdate Macro
'
Workbooks.Open Cells(1, 1).Value
Range("D6: D16").Select
Selection.Copy
Windows("Collected Balance Beta Test File.xlsm").Activate
Range("L7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub