I need help creating a user interface that will prompt the user to enter in the names of various spreadsheets so the macro can call upon them when it runs.

I am working on a macro in excel 2003 to consolidate information from six excel spreadsheets. There is a main excel workbook titled “accounts_nov11” and five other workbooks with additional information. The macro copies the information from “accounts_nov11” into a new workbook, adds new columns, and finally pulls in information from the five other spreadsheets using dynamic vlookup functions.

The Macro works, however, I am running into a problem because the spreadsheets are created by different depts. and name of the spreadsheets change every month (ex. next month the name of main spreadsheet will be “accounts_dec11”). I want to create a user interface that will prompt the user to enter in the name of each of the spreadsheets before the macro runs, so the macro knows which files to call, and then click a button to run the macro. I have pasted a small portion of the code for reference that shows the vlookup for the first two support files - USERS_Nov11_Query457.XLS & NEW DEPTS_nov11.xls (this file name will change next month):

sub AcctMacro()

Application.ScreenUpdating = False

Dim y As Long
Dim x As Long

Workbooks.Add
ActiveWorkbook.SaveAs (["Consolidated file"])
Windows("accounts_dec11.xls").Activate
Cells.Select
Selection.Copy
Windows("Consolidated file.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select

'Adds the first column and adds "ACCOUNT CATEGORY" column w/dynamic vlookup
Range("F:F").Insert
Range("F1").Formula = "ACCOUNT CATEGORY"
Range("F2").Select
Range("F:F").NumberFormat = "General"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC5,OFFSET('ACCT CATEGORY_nov11.XLS'!R2C1,0,0,COUNTA('ACCT CATEGORY_nov11.XLS'!C1),3),3,FALSE)"

y = Range("E65536").End(xlUp).Row
Range("F2").AutoFill Destination:=Range("F2:F" & y), Type:=xlFillDefault

‘ Second vlookup
Range("CE2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-73]:R[25]C[-73],OFFSET('[NEW DEPTS_nov11.xls]Sheet2'!R2C1,0,0,COUNTA('[NEW DEPTS_nov11.xls]Sheet2'!C1),2),1,FALSE)"

x = Range("J65536").End(xlUp).row
Range("CE2").AutoFill Destination:=Range("CE2:CE" & x), Type:=xlFillDefault

End Sub