Macro to copy two Sheets matching Source to each workbook in matching Destination Folder

Hello All
I am using Excel 2010

I have a many workbooks in Folder C:\Source\Master\

The workbook has many sheets out of which two Sheets are Dept and Result

The names of the workbook in C:\Source\Master\ folder are in the following format.

A number followed by Alpha characters.
1. Abcd
2. Efgh
3. Hij
4. Klm
5. Nopq
6. Rstu
7. Vx
8. Wyzab
9. Cdefghi
10. Jkl
….
….
And many more

I have different folder eg: C:\Destination\ which has the same naming conventions as shown above.

This will have one to many workbooks

I wish to have a macro which should run as follows:

a) Go to Folder C:\Source\Master\
b) Open eg: 1. Abcd
c) Copy only sheets Dept and Result
d) Save and Close
e) Go to C:\Destination\
f) Open the folder matching the name in b) above eg 1. Abcd
g) Paste the two sheets Dept and Result in the last
h) Save and close
i) Do this for all workbooks
j) Repeat steps b) to h) for others
k) Continue till all the folders are finished

There may also other folders and files which I don’t want in C:\Destination\
Only names matching in both Source and Destination are required

It should match a numeric followed by Alpha characters and do the copy paste in matching folders

Hope to get some help

Thanks in advance

Rashid Khan

Hi JLGWhiz


I am using Excel 2010.

I have tested the path and the spelling..they are all fine.

I am sending you a sample folder 1. Abcd with two files for sources as well as destination

Kindly try it on your side please

Thanks for your time once again.

I am confused. The original post indicates that 1. Abcd is a file name, but the link in Post #5 shows it as a subdirectory containing two files. The code will not work unless the directory path and the files are properly referenced. So you need to be specific about which is which and then maybe I can do something with the code. As I said, right now I am confused and not sure what to try.

Originally Posted by JLGWhiz

I am confused. The original post indicates that 1. Abcd is a file name, but the link in Post #5 shows it as a subdirectory containing two files. The code will not work unless the directory path and the files are properly referenced. So you need to be specific about which is which and then maybe I can do something with the code. As I said, right now I am confused and not sure what to try.

Yes you are right.
My sincere apologies for that.

1, Abcd etc etc are folders in both places which have workbooks.

Can you please amend the code to match folder names on Source and Destination and do the copying.

Thanks and sorry once again for the confusion

Regards

Originally Posted by prkhan56

Yes you are right.
My sincere apologies for that.

1, Abcd etc etc are folders in both places which have workbooks.

Can you please amend the code to match folder names on Source and Destination and do the copying.

Thanks and sorry once again for the confusion

Regards

If you can give the correct path, workbook and sheet names in a manner that I can understand, then I can fix the code. At present, I feel that it would be futile to try.

Dear JLGWhiz,

Please see the scenario

Path 1 - C:\Source\Master\ with following folders as follows

1. Abcd
2. Efgh
3. Hij
4. Klm
5. Nopq
6. Rstu
7. Vx
8. Wyzab
9. Cdefghi
10. Jkl

The above folders will each have a workbook which has many sheets names can be any name out of which two Sheet names are Dept and Result. I want the Dept and Result sheets only to be copied to all workbooks in matching folders in Path 2

Path 2 - C:\Destination\ with following folders as follows

1. Abcd
2. Efgh
3. Hij
4. Klm
5. Nopq
6. Rstu
7. Vx
8. Wyzab
9. Cdefghi
10. Jkl

The above folders each will have one to many workbooks which has many sheets where the two Sheets (viz. Dept and Result) from Path 1 should be copied in the last in each workbook


There may also be other folders and files in Path 2 which I don’t want.


Only folder names matching in both Path 1 and Path 2 are required for copying

My objective is to match the folder names in both Path 1 and Path 2 and do the copying from Path 1 to Path 2

Hope it is clear now

Thanks for all your time and help

See if this will work.

Please Login or Register  to view this content.

Hi
When I run the code it says compile error: For without Next

Thanks and regards

put 'Next' where shown below

Please Login or Register  to view this content.

Hi JLGWhiz,
I changed the code.

It gave me error - see Macro Error 1004
and highlighted the code - see Error Code

My path is correct - see Path

Hope you can please see into this problem
Thanks for all your time and help

Change this:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Hi JLGWhiz,
I changed the code.

Now two issues are coming.

See the screenshot 1004 Error.

Your code is looking for the same file name where as I explained in the scenario it is not
the same name for workbooks both in Path 1 and Path 2. The workbook names are different in both Paths
I want to have the folder names matching for copying purposes. Hope it is clear now.

For sake of testing I made the filenames same in both Path 1 and Path 2 then other error comes up

See the screenshot - Error code if same file names

I think you have to tweak the code further please

I am also enclosing 1. Abcd folder both (Source and Destination) for testing purpose

Thanks once again for all your time and help

Hello JLGWhiz

I tested your code it works fine if there is only one workbook in the Path 2 - Destination folder.
I have mentioned in the Scenario that I want the Dept and Result sheets to be copied in all the workbooks in the Destination Folder matching folder names of Path 1 and Path 2.

I have tested it with single workbook in each folder of Destination then the code works fine but when there are more than one workbook in the Destination folder then the macro gives sheet object error - see the pic and highlights the code (see pic).

Can you please amend it accordingly?


Also one small favor please if you could add an error trap as while testing I noticed that if there is no workbook in the Source then it pops up with the error 'File not found' window.

Thanks a lot for all your time and help

It is obvious that I don't have a good understanding of how your workbooks are set up within each directory, and I have already spent several hours on this, so I am going to pass on this thread. Maybe someone else will pick up on it and give it a try. Sorry I couldn't resolve the issue for you.
Regards, JLG

Hello JLG,

Thanks for all the time and help.

I hope someone will come up with the final solution.

Regards
Rashid