File Names within a Macro Function

[John]

I am trying to create a generic macro that will run a serious of various
commands from one file to another file. The trick is "File A's" name changes
daily. For instance "File A 12_2", "File A 12_3", "File A 12_4", ETC.... Is
there a way to
tell the macro to run off of File A even though the name changes daily?


Sub Amazing()
'
' Amazing Macro
' Macro recorded 12/4/2008 by John
'
' Keyboard Shortcut: Option+Cmd+e
'
Windows("File A 12_2.xls").Activate
Sheets("Agent").Select
Rows("6:2000").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="total"
Selection.Sort Key1:=Range("D7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Selection.AutoFilter Field:=4, Criteria1:="BOB"
Rows("7:2000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("File B.xls").Activate
Range("A3").Select
ActiveSheet.Paste
Windows("File A 12_2.xls").Activate
Selection.AutoFilter Field:=4, Criteria1:="Joe"
Rows("7:2000").Select
Application.CutCopyMode = False
Selection.Copy
Windows("File B.xls").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("File A 12_2.xls").Activate


This is an example of the coding I am trying to use. File A's name changes
every day. I want to be able to maybe put a general file name or command in
place of "File A" that would allow me to run this macro on the open file. I
would open "File A 12_4" and run the macro and it would pull the info from
the report and place it on File B. With File B the macro will rewrite over
the previous information. Any help????

 

[Shane Devenshire]

Hi,

it seems that there is a pattern here:
Windows("File A 12_2.xls").Activate
If this macro runs on 12/3 or 12/2 consistently then

myName="File A "&MONTH(NOW())&"_"&DAY(NOW())&".xls"
Windows(myName).Activate

 

[John]

I think this should help but "file a 12_2" changes daily. The 12_2 part is
based on the day. I want to run this macro from "File A" daily and the date
part of the file will change every day. How can I make it work based on the
file name changing daily. The first part of the file will not change just the
date. Also where in the macro do I put the command below?