I have 4 individual workbooks that are generated as part of our
monthly/quarterly reporting and want to automate the process of combining
each workbook into one file with 4 worksheets. Each workbook has the same
data/columns and similar naming convention. The name of the worksheet in
each file is the same as the file name (see below).

Sheet Name Workbook Name
FIN_20080630 FIN_20080630.xls
HRS_20080630 HRS_20080630.xls
GEN_20080630 GEN_20080630.xls
ISS_20080630 ISS_20080630.xls

I found the code below in a post by Tom Ogilvy and was able to modify it for
my needs by hard coding the file names. What I would like to do is create a
template with a button to prompt the user for the cycle date and pull the
corresponding files into a new workbook. I would also like to know if it's
possible to run the TRIM function on a specific column in each of the files
as part of the process.

Sub Combinebooks()
Dim sPath as String
Dim bk1 as Workbook
Dim bk2 as Workbook
Dim bk3 as Workbook
Dim bk4 as Workbook

spath = "C:\Documents and Settings\en14259\Desktop\EOM Reports\"

if dir(sPath & "Consolidated20080630.xls") < "" then
kill sPath & "Consolidated20080630.xls"
End if

set bk1 = workbooks.open(spath & "FIN_20080630.xls")
set bk2 = workbooks.Open(sPath & "HRS_20080630.xls")
set bk3 = workbooks.Open(sPath & "GEN_20080630.xls")
set bk4 = workbooks.Open(sPath & "ISS_20080630.xls")
bk2.worksheets(1).copy After:=bk1.worksheets(1)
bk1.worksheets(2).name = "HRS_20080630"
bk3.worksheets(1).copy After:=bk1.worksheets(2)
bk1.worksheets(3).name = "GEN_20080630"
bk4.worksheets(1).copy After:=bk1.worksheets(3)
bk1.worksheets(4).name = "ISS_20080630"
bk1.worksheets(1).name = "FIN_20080630"
bk1.SaveAs sPath & "Consolidated20080630.xls"
bk1.close Savechanges:=False
bk2.close Savechanges:=False
bk3.close Savechanges:=False
bk4.close Savechanges:=False
End Sub