Copying multiple workbooks into 1 template workbook

[spammenotspam]

Dear All,

this might sounds stupid question for experts here, but i have yet to read useable solutions for my problem.
I have 3 workbooks, each hv different worksheets in them, and i wish to copy them all into 1 template workbook where i send to clients.

I believe this might works, but need further help to troubleshoot them.
Workbooks.open("ABC.xls")
Workbooks("ABC.xls").Sheet1.Select
Workbooks("ABC.xls").Sheet1.Name = "9. AAA"
Workbooks("ABC.xls")Sheet2.Select
Workbooks("ABC.xls").Sheet2.Name = "10. BBB"
Workbooks("ABC.xls").Sheet3.Select
Workbooks("ABC.xls").Sheet3.Name = "11. CCC"
Workbooks("ABC.xls").Sheets(Array("9. AAA", "10. BBB", "11. CCC")).Copy Before:=Workbooks(final workbook_ddmmyyyy.xls").Sheets(18)
Workbooks("ABC.xls").close

This code should resides in destination/template workbook called Final workbook_ddmmyy.xls, and tries to copy worksheets from ABC.xls or other 2 xls files within same folder.

the issue with up there is, I want the save destination file name to be variable, as the name of the file would change on daily basis, and i do not want to edit my macros on daily basis for that particular destination file name only.

Please help instruct. Your help is greatly appreciated.

 

[spammenotspam]

it's not gonna work?
how about, i want to save as a workbook as workbook name +today's date in ddmmyyyy format?.. please help. thanks