Merge 5 worksheets in to one

You could try using Insert->Object (Create from File). This would give you a picture of a worksheet that you can then Edit.

You don't have to this a cell at a time. Start with a single cell then drag the cell pointer to fill cells.

Open your personal WB and, in turn, open a target workbook. On each sheet in your workbook type: = , then click in a single cell in the target WB, press enter.

Example result: [Book2]Sheet1!A1

Next, drag this across rows and down columns as needed for as many rows and columns as are in the target WB. Repeat for all target WB's.

hi

With Palmetto's version you need to make sure the formula finishes as "!A1" without any dollar signs (ie not "!$A$1") before dragging it down & across.

When duplicated information exists there is a greater risk of errors.
Are separate files really necessary (eg is some info confidential to each Sales person)?
Could the files be combined into a single version which people access at different times?
(I suggest "different times" because "shared workbooks" seem to be more at risk of becoming corrupted)

Here's an extension to Palmetto's suggestion which allows you to have exactly the same formatting as the other file...
(Palmetto posted the succinct version as I was typing up the below...)


When you have their file open & your file open, select cell A1 on the sheet in their file, press [ctrl + shift + end] & [ctrl + c] to select & copy the entire "used range" of the spreadsheet, activate your file & select cell A1 & [ctrl + v] to paste everything in (if the sheet includes formulae, this could cause an error - but ignore it). If you need to copy column widths as well, recopy their file, select A1 in your file & press [alt + e + w], [enter] & [esc].
Now you have a copy of their sheet in your file with all the same formatting but it is not linked. To change it to links:
- delete all the info using the [delete] key on your keyboard or [alt + e + a + c] (ie "clear contents" through the Menu - NOT "Edit - Delete...").
- Select A1 on their sheet & press [ctrl + shift + end] & [ctrl + c] to select & copy the entire "used range" of the spreadsheet
- activate your file & select cell A1 & press [alt + e + s + L] to paste links.

Note, this approach may not be very effective if there are a lot of blank cells in their file (which may show up as "0" in your file - depending on your formatting), but you can use Find & Replace ([ctrl + h]) to get rid of most of the unnecessary links quickly by searching for 0 in formulas, find all, selecting the relevant ones in the popup, pressing [esc] to close the Replace dialog box, & then pressing the delete key on the keyboard.

Another option could be to have your file set up (eg links to another sheet in your file) so that you can just copy & paste the whole of their sheet into a sheet of your file as you open the file. would you be happy to have a macro in your file?
The process could even be automated by a macro if you can provide the file path for each of the files or are happy to select the relevant files from a popup.

hth
Rob