open excel files in a sub-folder and copy sheet to another worksheet.

arcesdiola

New Member
Joined
Mar 6, 2011
Messages
3
Hello guys,

May you help me with my project?

I need to open "all the files" in a specific sub-folder inside a folder. files inside the sub-folder do have specified date on its sheet name (also with the file name, correspondingly) and close the files after copying. here are the details.

main folder: D:\Documents and Settings\arces.diola\Desktop\teams
sub folder: \AO
sub-sub folder: \20110115-20110215
files: john smith_20110115-20110215.xls
jane close_20110115-20110215.xls
james reid_20110115-20110215.xls
(inside john smith_20110115-20110215.xls file, name of the worksheets are 0115-0131 and 0201-0215, and same for the other excel files)
the sheets to be copied will be placed to compilation.xls

i hope you can help me with my problem. thanks..
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
If you have 3 files you are probably much better off copying the sheets by hand. But if you must automate the process look at
Process all files in a folder and, optionally, in sub-folders
http://www.tushar-mehta.com/publish_train/xl_vba_cases/process_all_files_in_folder.htm

Use the 'ProcessOneFile' subroutine to open the indicated file, copy the worksheets, and close the file without saving it.

To get the necessary code, use the macro recorder. Once you have the code from the macro recorder, adapt it yourself or share it here and someone should be able to generalize it for you.
 

arcesdiola

New Member
Joined
Mar 6, 2011
Messages
3
Hi Tushar,

I use Marco Recorder and these are the codes I got opening the files inside the sub-sub folder and copying particular sheets on the "compilation.xls"

'opening james reid_20110115-20110215.xls
Sheets("jr_0115-0131").Select
Sheets("jr_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(1)
Windows("james reid_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening jane close_20110115-20110215.xls
Sheets("jc_0115-0131").Select
Sheets("jc_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(2)
Windows("jane close_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening john smith_20110115-20110215.xls
Sheets("js_0115-0131").Select
Sheets("js_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(3)
Windows("john smith_20110115-20110215.xls").Activate
ActiveWindow.Close
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Originally, you'd indicated that the worksheet names would be the same for each of the workbooks. In the sample you shared, they are different.

You can program all this but it will get increasingly messier as you customize the code for each different workbook.

Are you sure you can't process the 3 workbooks by hand?
Hi Tushar,

I use Marco Recorder and these are the codes I got opening the files inside the sub-sub folder and copying particular sheets on the "compilation.xls"

'opening james reid_20110115-20110215.xls
Sheets("jr_0115-0131").Select
Sheets("jr_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(1)
Windows("james reid_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening jane close_20110115-20110215.xls
Sheets("jc_0115-0131").Select
Sheets("jc_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(2)
Windows("jane close_20110115-20110215.xls").Activate
ActiveWindow.Close

'opening john smith_20110115-20110215.xls
Sheets("js_0115-0131").Select
Sheets("js_0115-0131").Copy After:=Workbooks("compilation.xls").Sheets(3)
Windows("john smith_20110115-20110215.xls").Activate
ActiveWindow.Close
 

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top