Update a macro with new name of wkbook

[Gingertrees]

I create a new workbook each day (titled as PREV.DAY'SDATE) by copying the previous day's workbook and changing the file name. I recorded a macro in today's workbook, and everything about it will work except all the mentions of the filename. How can I change all mentions of "9Aug11.xls" to the current filename, be that "10Aug11.xls", or later "8Sept11.xls", etc.?

Here's a little sample of code to change:

    Windows("text1.txt").Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("9Aug11.xls").Activate
    Range("B5").Select
    ActiveSheet.Paste
    Windows("text2.txt").Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("9Aug11.xls").Activate

 

[Comfy]

Dim wb As Workbook
Set wb = Activeworkbook
Windows("text1.txt").Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    wb.Activate
    Range("B5").Select
    ActiveSheet.Paste
    Windows("text2.txt").Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    wb.Activate

 

[Gingertrees]

There may be a problem with that: each of those .txt files? Those have actually just been copied into excel (I just haven't saved them because they only exist temporarily). So I think ActiveWorkbook would not always point to the file I want...

I think what I'm looking for (if it exists) is for the macro to look at the filename, and replace "9Aug11.xls" with whatever that is (13Sept11.xls...5Dec11.xls...etc).

 

[Comfy]

Dim fn As String
fn = Application.GetOpenFilename
fn = Dir(fn)
Windows("text1.txt").Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows(fn).Activate
    Range("B5").Select
    ActiveSheet.Paste
    Windows("text2.txt").Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(fn).Activate

There is no validation in this so I you hit Cancel in the File Select screen the MAcro will die

 

[Gingertrees]

Thanks! That worked like a charm.