Open All Excel Files in Folder and Copy and Paste Data in Macro-Running Workbook

I am having trouble with my VBA code for my macro that I want to open the msoFileDialogFolderPicker and the user pick a folder in which all the excel files will be opened and one by one data will be copied from the newly open workbook and pasted into specific sheets in the workbook where the macro is running. Basically we give each of our sales rep a spreadsheet to fill out of their sales and then they submit their spreadsheets to the sales manager. What I want to do instead of someone having to open up each spreadsheet and copy the data and paste all of it into one spreadsheet manually, is to simply have a macro that does this for me. Since the location and names of the files can change, I am trying to make it as dynamic as possible. There may be a better way of doing this, so any suggestions are much appreciated!

The issue I am having is that I get the files to open and they copy, but then I get a Run Time Error 1004 'Copy method of Range Class Failed' when I try to get it to paste in the workbook that is running the macro. I have tried ThisWorkbook and ThisWorkbook.Activate to try and tell Excel to go to the spreadsheet where the macro is running, but none solved my problem. Sometimes I get past the error but it still never pastes the data in the master workbook. I have my code written below. Admittedly, it has been mostly copied from code I have found but I have tried to adapt it for my purpose. The line I am getting the error on is the "wb1.Worksheets(1).Range("A5").Select" line.

Please Login or Register  to view this content.

This is a simplified version of what I am eventually going to do, which includes copying things from multiple worksheets in the newly opened workbook and pasting them in multiple sheets of the originally macro-running workbook. At this point however, I am just trying to get this simple version to run and work. Thank you all for all your help and my apologies for the long code, but I want to give everyone an idea of exactly what I am doing. Thanks!

Okay, I see what you are saying. I've gotten the error to go away. Also through some other research I found that I can use source to set destination and quickly copy and paste things. My follow up question, further relating to the more complex version I need, is how would I go about doing this across multiple workbooks, without pasting over the data thats already there? Essentially, how do I loop yet also tell the loop to paste 500 rows below the last paste, or just paste below the last row of data, every time it loops? We were thinking of giving the sales people 500 rows, which is way more than enough, that way we could just set the macro to go 500 rows and paste every time. However, if there is some function that will just go one row below the last row of data and paste there every time, that would be the ideal solution. Any ideas on how to do that? Again, I need more help with doing this in a loop, as I can probably figure out how to do it for just one instance.