How to copy a full sheet in formulas to a new Workbook

One the worksheet to be copied

Open a new workbook (shortcut CTRL+N)

Arrange them on monitor so bot can be seen

With CTRL key held down; drag the tab of the source sheet to the tab zone of the new book; lease mose key and CTRL

The new book as exact copy of all data and formulas

best wishes

Hello Rajesh:

Glad you liked this 'trick'.

I come to this forum to answer questions but often end up learning something new.

Best wishes from Nova Scotia

Carry out the following steps:

1. Right-click on the worksheet which you wish to copy to another workbook
2. Select "Move or Copy"
3. In the box under "To book:"  select the other workbook to which you wish to copy the worksheet in.
4. Under the "Before sheet:", you can select the worksheet, before which , this worksheet should be copied
5. Click OK

However, you must note that this method will definitely copy your worksheet to the other workbook, but the formulas in that worksheet will still refer to the old workbook.

In case if you wish that even the formulas (just in case if your design is identical) should refer to the new workbook, then there is another method.

Here in this case,

1. you need to replace all the formulas of your worksheet-to-be-copied as text

(you need to replace =A1+B1 type of formula with ~=A1+B1)

2. After the copying is done to the new workbook, replace all formula text  (~=.......)  with =.... by replacing ~= with =

Hope this Helps.

Wow Bernard!

This will be awesome!

I never thought about this option.

Thanks for sharing this.

Hi,

Right click on the tab and select Move or Copy > Copy.  Select a new workbook there.