Choose folder & loop through all files to copy - folder is subfolder in network drive

I have been working on something that really has me stumped, even after searching the internet for days (although I must admit I'm still quite the novice.)

I want my code to copy the data from a specific sheet (same name in each file) for each file in a folder and paste it into my workbook - essentially combining the data from each file in a given folder to create a master summary sheet.

Here's the step by step for what I want it to do:

--------------------------------------------------------------------------------------------------------------------------------

1. Prompt the user to choose the folder containing the excel files (this folder will be a sub folder on a network drive)

2. Loop through every workbook in the chosen folder and for each workbook in the folder, do the following

A. Go to the "SUBMITTED BUDGET SUMMARY" worksheet and copy all of the data in the table (table name = "Summary")

B. Go to Sheet1 of my master file "MASTER BUDGET SUMMARY - ALL BUDGET OFFICERS.xlsm", find the next blank row and paste the copied data as a pastespecial - paste link - one row below the blank row (leaving blank rows between each of my copied data)

C. Close the workbook I just copied data from without saving

3. Move on to the next workbook and repeat steps A thru C above.
--------------------------------------------------------------------------------------------------------------------------------

I initially wrote and compiled code from doing some research online and tested it choosing a folder on my desktop. It worked beautifully, but now it won't work when I try to choose the ACTUAL folder containing my files, which is a sub folder on a shared network drive.

I've looked everywhere at various possible solutions and I can't figure it out!!! Any help would be MUCH appreciated!

Here's my code:

Please Login or Register  to view this content.

Thanks, Mike.

I was actually recommended to check out this Ron de Bruin code (link below) and I was able to modify it a bit for my purposes. But being a nube at this, I'm not sure how/where to change the code so that instead of pasting the data as values, it will paste special - paste link (I need links because users may be changing data in the original workbooks and I need to make sure that it's also changed in the master summary workbook.)

Also, Ron de Bruin's code creates a new workbook as the "master" workbook where it pastes everything - but I already have my "master" workbook where I want it to paste. This workbook will be open and the code will be assigned to a button for the user to click. The name of my workbook will be "MASTER BUDGET SUMMARY" and the sheet it should paste the links into will be "Sheet1". Any idea how I could change the code to paste into my existing workbook instead of creating a new one? I'm really having a hard time deciphering this one!

Here's the link to Ron de Bruin's code: http://www.rondebruin.nl/win/s3/win008.htm

Here's my edited code from Ron de Bruin (not including the basic codes in used to get the file names, find last cell, etc.):

Please Login or Register  to view this content.

At the risk of sounding stupid, why do you NOT want links?

Thanks for explaining!

I completely understand what you're saying, and normally I would go that route. But with this particular project, I've got between 60-70 workbooks that I'll be combining from the folder I choose. Each of those workbooks has anywhere from 1-40 worksheets, and each of those worksheets has over 300 rows where data is entered.

So I don't want to have to re-run my macro every time I open the file and have it go through ALL 60-70 workbooks again to copy and paste data, just because one entry line was changed on one worksheet. Does that make sense?

Ha! Mike - I totally get what you're saying. Although, I'm wondering now if you and I are not talking about the same kind of links? (again, don't judge the newbie)

In my original code above, I had it pasting links (just like if I had manually gone and copied the data and then went to the paste special drop down and chose "paste links") and there was no kind of refreshing to be done when I tested changing data in my source workbook - it was just automatic. Are you talking about going the insert-link route?

But let's say I go your way and paste the values. I still don't know how to paste those values into my existing workbook, instead of creating a brand spanking new workbook. To be honest, my original code makes more sense to me than my revised Ron de Bruin's . . . his is quite complicated and WAY over my head! (hence why I'm having a hard time figuring this part out.) I think that I'd need to change the code around this line (as evidenced by my little comment to self):

Please Login or Register  to view this content.

Any thoughts as to how I would change this so that it's pasted in my workbook ("MASTER BUDGET SUMMARY - ALL BUDGET OFFICERS.xlsm"), in my worksheet ("Sheet1")?

Sorry - I just posted a snippet of the code where I thought I'd need to change it as the full code is already given above.

I get what you're saying about clearing out the data tab and having a tab labeled data, I still haven't addressed the issue of having it paste the data into my workbook on my data tab - not a new workbook that is created with a tab named "Combine Sheet".

I need it to paste into my existing workbook as I have additional code that creates a pivot table from the data with special formatting and the special format (as well as the additional code) resides in my workbook. Ultimately I'm going to hand all of this over to another user, so I want all of the code and formatting to remain in the workbook.

So how would I modify the code to have it paste into my workbook and my worksheet?

Thoughts?