Using Date$ function in file name
 

I am trying to get code built into a worksheet that will allow me t
toggle between two sheets that I have open. The problem is that one o
the files will have one name when a portion of the code is run, an
will be saved as a new name before the rest of the code runs (e.g.
Wells_ACH.xls will be saved as Wells_ACH-11-02-05.xls). The new fil
will have the same name with the current date added for audit purposes
I have attempted to use the following to call the procedure, but I a
having trouble. Any help or suggestions would be appreciated.

In the worksheet with the button:

Private Sub CommandButton2_Click()

Current = Date$
Application.Run "Wells_ACH-&CurrentDate&.xls!Export_Data"

End Sub

In the module with the macro:
Sub Export_Data()

Application.ScreenUpdating = False
CurrentDate = Date$

'Export and Format Data from Column A
Windows("Wells_ACH-&CurrentDate&.xls").Activate
Sheets("ACH_Calculation").Select
Range("A:A").Select
Selection.Copy
Windows("ach import.csv").Activate
Range("A1").Select
ActiveSheet.Paste


'Export Data from Column B
Windows("Wells_ACH-&MyDate&.xls").Activate
Sheets("Previous_ACH").Select
Range("C:C").Select
Selection.Copy
Windows("ach import.csv").Activate
Range("B1").Select
ActiveSheet.Paste
Range("A1").Select


'Format Columns for Bank Upload
Windows("ach import.csv").Activate
Range("A:A").Select
Selection.NumberFormat = "0000"
Range("A1").Select


'Save and Close File
ActiveWorkbook.SaveAs Filename:= _
"J:\Treasury\Cash Forecasting\ach_module\ach calculation
spreadsheets\Wells Fargo\ach import.csv" _
, FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close

Windows("Wells_ACH.xls").Activate
Sheets("ACH_Calculation").Select
Range("A1").Select

End Su

--
tobrian
-----------------------------------------------------------------------
tobriant's Profile: http://www.excelforum.com/member.php...fo&userid=2515
View this thread: http://www.excelforum.com/showthread.php?threadid=48100