Copy range from multiple files in multiple folders to single sheet in master WB

I have 30 files, each in a different folder on a network drive. I need to be able to copy the top 100 rows from the first sheet of each file and paste them into a single sheet in a master file on a monthly basis. (If that is not possible, I guess I could copy them to separate sheets and link all of the sheets to a "master" sheet in my master workbook.)

There are other Excel files in these directories, but the files I need to copy this range from share the word "Source" in the workbook name.The sheet name of the first sheet in every file is "CASH".

The copy and paste from the first file would fill rows 1 - 100 on the master sheet. The data from the second file would paste on rows 101 - 200 in the master sheet, and so forth. After all the data has been copied and pasted the file would automatically save. Some directory examples:

S:\Accounting\Film\WOLB\WOLB Source File.xls

S:\Accounting\Film\WITX\WITX Source File.xls

S:\Accounting\Film\WBBB\WBBB Source File.xls

If you can help me I would REALLY appreciate it. I've been unable to find a solution to this anywhere.

Thanks!

@Royzer

I am getting this message "Compile Error: Sub or Function not Defined" when it gets to SetCurrentDirectoryA under Errhandler:

My fault, I did not include the function declaration.

But first can you clarify...

I need to be able to copy the top 100 rows from the first sheet of each file
The copy and paste from the first file would fill rows 1 - 100 on the master sheet

Also, sorry I did not specify the range. It needs to be A3:N103.

As you want rows 3 thru 103 and not 'the top 100 rows from the first sheet of each file' can you please clarify which row the data should be copied to in the Master wb. Is it row 1 onwards or row 3 onwards?

Thank you.

Sorry, again. The Master WB would have two rows of headings, so the incoming data would need to go on row 3 onwards.

@Royzer

Try this one.
Note I have also included the original 'close parent wb and save' request after the message functions but have commented it out. To enable the 'close' just remove the single quote at the beginning of the line.

Please Login or Register  to view this content.

hth

gmk



Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.

Thank you!

@Royzer

I'm pleased we got there.






hth

gmk



Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.

@Royzer

Ok, remove the backslash and see what happens.

Getting the same message with and without that last backslash.

---------- Post added at 12:51 PM ---------- Previous post was at 12:45 PM ----------

It has to be a problem with how my file location is set up. I changed the path to another directory, one that has no test files in it, and it returned the message Completed. No workbooks were transferred.

I will fool around with it some more and see what happens.

Thank you again for taking the time to help me!

@Royzer

Are you ok using the VBE editor?

Instead of numbers in the debug lines I provided earlier, to be more precise I've used names...

Please Login or Register  to view this content.

with these results...

Please Login or Register  to view this content.

You should see something similar.

hth

I replaced the section of code as you instructed and tried it again on another path. I did not get the results you show above, BUT it copied the data exactly how I need it! And I figured out why it was working on some paths and not the path on the S:\Accounting\Film path. It was looking for a third folder, apparently. I added another folder and put the test files in it so that they were in here:

S:\Accounting\Film\Film2

but kept the VBA code as:

Const sPath As String = "S:\Accounting\Film"

and it works perfectly!

GMK, I cannot thank you enough for your time and effort. I really appreciate it.

Roy

@Royzer

I added another folder and put the test files in it so that they were in here: S:\Accounting\Film\Film2

If you only put files into Film2 then Film2 acts like any of the other test folders WBBB, WITX, WOLB.
If I put those folders containing files into Film2 as you suggest I got the message "There are no files in any subfolders." which is exactly what I would expect because there is only one recursion level.

I did not get the results you show

If you didn't get the same results the directory paths cannot be as described.

Nonetheless, you do have a solution that works for you and that is the main objective.

gmk