How To Copy Multiple Sheets In Excel To New Workbook
The tutorial shares a few helpful secrets about copying and moving sheets in Excel.
There may exist many situations when you need to create a new spreadsheet based on an existing one or move a tab from ane Excel file to some other. For instance, you may want to support an important worksheet or create several copies of the same sheet for testing purposes. Luckily, there be a few easy and quick ways to duplicate sheets in Excel.
How to copy a sheet in Excel
Excel has iii inbuilt routes to duplicate worksheets. Depending on your preferred working technique, you tin can use the ribbon, mouse or keyboard.
Method i. Re-create Excel sheet by dragging
Commonly, you drag-and-drib to move something from one place to some other. But this method also works for copying tabs and is, in truth, the fastest fashion to copy a sheet in Excel.
Simply, click on the sheet tab that you want to copy, hold the Ctrl key and elevate the tab where you want it:
Method ii. Duplicate a sheet by correct-clicking
Here's another way to duplicate a sheet in Excel that is merely equally like shooting fish in a barrel:
- Correct click on the tab and select Move or Copy from the context menu. This will open the Motion or Re-create dialog box.
- Nether Earlier sheet, choose where you want to place the re-create.
- Put a tick in the Create a re-create box.
- Click OK.
For instance, that's how you lot can brand a re-create of Sheet1 and place it before Sheet3:
Method 3. Copy a tab in Excel using the ribbon
The ribbon contains all the features available in Excel, you only demand to know where to wait :)
To copy a sheet, become to the Home tab > Cells grouping, click Format, and then click Move or Copy Sheet:
The Motion or Copy dialog box appears, and you lot follow the same steps as described above.
How to copy Excel sheet to another workbook
The common style to copy a sheet to some other workbook is this:
- Right click on the tab that you want to re-create, and and so click Move or Copy…
- In the Move or Copy dialog box, do the following:
- Under To book, cull the target file. To place a copy into a new workbook, select (new volume).
- Under Before canvass, specify where to put the copy.
- Select the Create a copy box.
- Click OK.
Note. Excel displays only the open workbooks in the To book drib-down list, so be certain to open the destination file before copying.
Apart from this traditional route, at that place is ane more fashion to do the same thing. Judge what, simply by dragging a canvass from one Excel file to another!
How to copy a sheet to some other workbook by dragging
If Excel allows duplicating a sheet within the same workbook by dragging information technology, why not try to apply this method for copying a sail to a different workbook? We just need to view both files at the same fourth dimension. Here'southward how:
- Open the source and target workbooks.
- On the View tab, in the Windows group, click View Side by side. This volition arrange the two workbooks horizontally.
- In the source workbook, click the sheet tab yous'd like to copy, hold down the Ctrl key and elevate the sheet to the target workbook.
Awesome, isn't information technology? The next time you need to copy information from one Excel file to some other, forget almost re-create/pasting and drag the canvas.
How to copy multiple sheets in Excel
All the techniques that work for duplicating a single sheet can be used to copy multiple sheets. The key matter is to accept several worksheets selected. Here's how you can practise this:
- To select adjacent sheets, click on the first sheet tab, printing Shift, and click on the concluding tab.
- To select non-adjacent sheets, click on the showtime canvas tab, printing Ctrl, and click the other tabs one by one.
With multiple worksheets selected, exercise one of the following to copy them:
- Click on any of the selected tabs, printing Ctrl and drag the tabs to the desired position.
- Right-click any of the selected sheets and click Re-create or Motility. Then, bear out these steps.
- On the Dwelling tab, click Format > Movement or Copy Sheet. More than details can be establish hither.
To copy multiple sheets to another workbook, select them equally described above, and then follow i of these ways.
How to copy Excel canvas with formulas
By and large, you copy a sheet containing formulas like any other sheet. The formula references accommodate automatically in a way that works just fine in nigh cases.
If a sheet with formulas is copied within the same workbook, the formulas will refer to copied sheet unless you utilize external cell references that point to some other sail or workbook. For instance:
If you copy a canvas with formulas to another workbook, the formula references acquit in this style:
- References within the aforementioned worksheet, either relative or absolute, volition point to the copied sheet in the destination workbook.
- References to other sheets in the original workbook will all the same point to the worksheets in the original workbook. In the screenshot below, delight notice that the original file name [Book1] appears before the sheet name the 3rd formula in the copied sheet:
For the copied formulas to refer to the sail with the aforementioned name in the destination workbook, you can but remove the original workbook name past using the Supercede All feature of Excel. Here'due south what you need to do:
- On the copied sheet, select all the formulas you desire to modify.
- Press Ctrl + H to open the Supersede tab of the Detect and Replace dialog.
- In the Find what box type the name of the original workbook exactly as it appears in your formulas. In our case, we'd type [Book1].
- Exit the Supplant with box blank.
- Click Replace All.
Equally the issue, the formula in the copied canvass will change from
=[Book1]Sheet2!B1*[Book1]Sheet2!C1
To
=Sheet2!B1*Sheet2!C1
Copy information from one Excel sheet to another using formula
In case you don't desire to copy the unabridged sheet, just only some function of information technology, select the range of interest and press Ctrl + C to copy it. Then switch to another canvass, select the upper-left cell of the destination range and press Ctrl + 5 to paste the copied range.
For the copied information to update automatically as soon every bit the original data changes, yous can copy data from one Excel sheet to some other past using formulas.
For example, to copy information from jail cell A1 in Sheet1 to jail cell B1 in Sheet2, enter this formula in B1:
=Sheet1!A1
To copy information from another Excel file, include the workbook proper noun in square brackets:
=[Book1]Sheet1!A1
If needed, drag the formula down or to the right to pull data from other cells.
Tip. To get a reference to another workbook right, type the equality sign (=), so switch to another workbook, click the desired cell, and press Enter. Excel volition insert the reference for you automatically.
To copy information from several sheets into ane, delight see How to merge multiple sheets in Excel.
How to duplicate an Excel sheet using VBA
If you often demand to copy data from one Excel sheet to another, macros can save yous a lot of fourth dimension. Here are only a few examples of tasks that can be performed automatically:
- Macro to copy canvas and rename
- Macro to copy canvas and rename based on cell value
- Macro to copy sheet from another Excel file without opening
- Macro to duplicate multiple sheets
For more examples, delight check out How to indistinguishable a sail in Excel with VBA.
How to move sheets in Excel
Moving sheets in Excel is even easier than copying and can be washed with the already familiar techniques.
Motion a sheet by dragging information technology
To move a sheet(due south) in Excel, you simply select i or more tabs and elevate them to a new location.
For example, here's how you tin move Sheet1 and Sheet3 to the terminate of the workbook:
To move a sheet to another workbook, identify the workbooks side-past-side (View tab > View Side past Side) and then drag the sail from one file to another.
Motion a sheet via Move or Copy dialog
Open the Move or Copy dialog box either by right-clicking the canvas tab and selecting Move or Copy from the context card or by clicking Home tab > Format > Move or Re-create Sheet. Then, exercise one of the following:
- To move a sheet inside the aforementioned workbook, cull before which sheet to place it and click OK.
- To move a canvass from one Excel file to some other, select the workbook name from the To Book list, choose the "Before sail" and click OK.
Note. If the destination file already contains a worksheet with the aforementioned name, Excel will add a number in brackets to the stop of the moved sheet'due south proper noun. For example, Sheet i will become Sheet one (two).
Workbook Manager - quick way to motion and re-create sheets in Excel
If you ofttimes copy or movement sheets from one Excel file to another, our Workbook Manager can make your life a lot easier.
Run the tool by clicking the Workbook Manager button on the Ablebits Data tab in the Manage group:
Now, you lot have a tree-view of all open up workbooks and tin can easily elevate sheets between different files:
- To motion a sail or several selected sheets, drag them in the Workbook Director window.
- To copy one or more sheets, drag them while holding the Ctrl fundamental.
As you do this, the sheets in your workbooks are rearranged accordingly without you having to switch back and forth from ane file to another.
Y'all are welcome to download a trial version of our Ultimate Suite to endeavor this and threescore+ more than awesome tools for Excel.
Move or copy sheet in Excel not working
Usually Microsoft Excel copies and moves sheets without a hitch. If a certain worksheet refuses to be copied or moved, this may be because of the following reasons.
one. Excel tabular array
Yous won't be able to move or copy a group of sheets if one of those sheets contains an Excel table (vs. a range). Each such worksheet should exist dealt with individually.
2. Protected workbook
Moving and copying sheets are non allowed in protected workbooks. To check if the workbook is protected, go to the Review tab > Protect group and take a expect at the Protect Workbook button. If the push button is highlighted, it means the workbook is protected. Click that button to unlock the workbook, and and then move sheets.
3. Excel names
When copying or moving a canvass from ane Excel file to some other, you lot can get an error message that a sure name already exists, pregnant the source and target workbooks contain a tabular array or range with the aforementioned name. If information technology is only a unmarried mistake, click Yes to utilise the existing version of the name, or No to rename it. In case of multiple errors, information technology makes sense to review all the names earlier moving or copying sheets. For this, printing Ctrl + F3 to open up the list of names in the active workbook - you lot will exist able to edit or delete the names from there. For more information, please see How to define and use names in Excel.
That'due south how you tin can move or copy a sheet in Excel. I thanks for reading and hope to come across you on our blog next week.
You may also exist interested in
Source: https://www.ablebits.com/office-addins-blog/2018/12/12/copy-move-sheet-excel/
Posted by: allenbutia1993.blogspot.com
0 Response to "How To Copy Multiple Sheets In Excel To New Workbook"
Post a Comment