Gaura215 wrote:

I have gone through several threads. One of that was quite close:
http://tinyurl.com/kkw6ahr

However, I have a slight different problem.

I have a folder for year 2013, which will be 2014 obviously next year.
This folder contains multiple subfolder, and further subfolders in each
of them, and so on. My objective is to copy all the excel files in
folder "2013" irrespective of which subfolder it is lying into. And want
to copy all those .xls files in a seperate folder which I will create on
my desktop. The names of the excel files can be same or may be
different.

I want to know if there is a way out to this. Please be noted, as
mentioned above, that there may be more than 1 file with same name in
few of these subfolders. So can our macro automatically give a suffix to
the file in the destination folder. For example, 3 files in Directory
"2013" with name "AAA". Can the macro save these three files as "AAA",
"AAA_1" & "AAA_2". Alternatively, I am fine with it, if the files are
saved with the path name from where they have been copied.

Any help in this would be highly appreciable. Please let me know if more
details needs to be furnished here.

There are a number of ways you can do this. What you do depends on what you
do and do not want copied.

The simplest way is to open a cmd prompt, navigate to the directory
containing \2013, and use xcopy:

cd /d "C:\users\auric\Desktop"
xcopy .\2013\*.xl* .\2014\ /s

This will copy all Excel files, and nothing else, to 2014, preserving the
directory structure (which you said was acceptable). For a list of switches
supported by xcopy, type "xcopy /?" (without quotes) at a command prompt.

Other methods (in VBA) involve walking the tree, checking whether or not a
given file already exists, keeping track of the current number to be
appended to the filename, etc. Bit of a PITA.

--
- You seem a decent fellow. I hate to kill you.
- You seem a decent fellow. I hate to die.