Exact copy worksheet mirror

Z

Z

Is there a way to make an exact copy of a worksheet (which includes row
spacing/background colors, forumlas), and put them into another excel
file as a new worksheet.

ie.
sheet1 of newbook.xls

is exactly the same as, and is also updated when sheet1 is modified.

sheet3 of masterbook.xls

any thoughts?
 
P

Pete_UK

If you want an exact copy of a sheet, then arrange the file window to
be smaller than the Excel window and CTRL-drag the sheet to the grey
area outside the file window. This will probably be named Book1.xls and
can be saved with a different name using File | Save As .. or can be
moved directly into another open file window.

It will be an exact copy at that point in time - it will not, however,
be linked in any way to the original, and so will not be updated when
the original is modified.

Hope this helps.

Pete
 
T

Traveller

I think what you want is Edit/Move or Copy Sheet, and click on "Create a
copy." This will give you a copy of the worksheet with links that will be
updated when you modify the original.
 
T

Traveller

I STAND SOMEWHAT CORRECTED (I'll correct myself before someone else does it
for me)!! Using the "Move or Copy Sheet" command will create links to lookup
tables in the orginal file, but changing data values in the original
worksheet will not carry over to the copied sheet.

It is possible to do exactly what I think you want to do (that is, create a
mirror worksheet that will reflect every data change done to the original),
but as far as I can tell, it is a bit of a complicated process without
writing code, but it can be done, and here is the way (I tested it):

1. Open the sheet you want to copy and make sure it is not "Protected."

2. Open an empty sheet in the target file.

3. Arrange the two files so you can see both sheets.

4. Using the Paste Special/Format command, copy the format of the original
to the target sheet.

5. Click on cell A1 in the original sheet and press CTRL-V to copy it to the
clipboard.

6. Click on cell A1 in the target sheet and choose Paste Special/Paste Link
(button on the bottom of the menu).

7. The formula in the pasted cell will end in "$A$1." Delete the dollar
signs so it ends in "A1."

8. Linking to empty cells will give you a "0" in the target cell, so use the
IF function like this: =IF(LINK="","",LINK) [where LINK is the orginal link
formula]

9. Now you can copy and paste the formula from cell A1 in the target sheet
to the rest of the active range using the Paste Special/Formulas command, and
the job is done.

10. IMPORTANT: DON'T TRY TO LINK THE ENTIRE ORIGINAL SHEET TO THE TARGET
SHEET IN ONE STEP. TRUST ME. You could link a range (but NOT the entire
sheet) in the orignal sheet to the same range in the target sheet and then
use conditional formatting to make the 0's white (rather than using the IF
function as I've suggested), but that would create a problem if you want
cells with legitimate "0" values to be visible in your target sheet.

There might be a better, simpler way to do what you want than the 10-step
process I've suggested, but I don't know of any. If any gurus out there have
a method, I'd love to see it.
 
Top