I am trying to get code built into a worksheet that will allow me to
toggle between two sheets that I have open. The problem is that one of
the files will have one name when a portion of the code is run, and
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 file
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 am
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 calculations
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 Sub


--
tobriant
------------------------------------------------------------------------
tobriant's Profile: http://www.excelforum.com/member.php...o&userid=25155
View this thread: http://www.excelforum.com/showthread...hreadid=481007