Your sample file is not very clear: in "Combine" sheet, Sam's data is permanent, but Bob's data is still linked to the source data. You want to copy permanent data from all sheets into one sheet, or you want to keep the links in the source data?
For consolidating data from all sheets in a summary workbook, you have many solutions on this forum. You might take a look at this: http://msdn.microsoft.com/en-us/library/cc793964.aspx
How to insert the code in your workbook: press Alt+F11, press Ctrl+R, right click the project with your workbook name in the left side and select insert new module, then in the new module window on the right side paste the codes. To run a macro, press Alt+F8, then select the macro name from list and press Run.
Here is how to find similar threads in this forum:
http://lmgtfy.com/?q=copy+range+from...eforums.com%2F