Overcome Problems when Copying and Duplicating Excel Worksheets
Copying worksheets in Excel is a common task. It provides massive time savings by allowing you to use the content of the worksheet over and over without the need to regenerate the formulas or the formatting. Most often copying a worksheet within the same workbook is easy and mostly problem-free. However, if you are trying to copy worksheets with a table or worksheets to a different workbook that contains formulas, then you might encounter problems. The aim of this article is to set out how you can copy worksheets and how you can overcome some of the problems you might encounter.
How to copy a worksheet or multiple worksheets to the same workbook
What happens to Formulas and Formatting and Tables when you copy a worksheet to the same workbook?
How to copy a worksheet or multiple worksheets to a different workbookWhat happens to Formulas and Formatting and Tables when you copy a worksheet to the same workbook?
What if you do not want to copy the reference sheets to a different workbook?How to copy or duplicate an Excel worksheet or multiple worksheets to the same workbook
To copy one or more worksheets to the same workbook, select the tabs that you want to copy. To select multiple tabs, hold down control as you left-click on the mouse to select each tab.
Once you have selected the tabs to be duplicated, right-click to open the tabs menu.
From the tab’s menu, select Move or Copy. This will open the Move or Copy Options box
The default setting in Move or Copy is to copy the selected worksheets to the same workbook. If you wish to copy the worksheets to a different workbook then you would use the To book dropdown. This will allow to select a different Excel workbook, but we will look at that later in this article.
You will also note, the first worksheet appears to be selected in the Move or Copy option box. This is deceiving, as we have selected the worksheets before we opened the box. In this case, we selected the worksheets Calculations and Sheet3, however Data sheet is highlighted in the Move or Copy options box.
To copy the selected worksheets, ensure you have included a tick on the Create a copy box and press ok.
Excel will now make a copy of these sheets and give them a default name as the original sheet name with a number at the end.
When you are making a copy of these worksheets, if you want the placement of the tabs to be at the end, you must select (move to end) in the Move or Copy options box.
What happens to Formulas and Formatting and Tables when you copy a worksheet to the same workbook?
When you copy a worksheet with formulas into the same workbook, all relative and absolute references should remain in place in each formula, relative to the new worksheets. However, when you are copying worksheets with formulas to another workbook, you may encounter problems, but we will deal with that later in the article.
When you copy a worksheet or group of worksheets the formatting on each sheet will also copy across to the duplicated sheet.
It is possible to copy a worksheet that contains a table within the same workbook. To copy a worksheet with a table, follow the same method as above, by first selecting the worksheet tab that you want to copy.
A problem occurs when you want to copy multiple worksheets and one or more of these contains a table. In this case, Excel will give you an error.
When you need a copy of a worksheet with a table, you must select the worksheet that contains the table on its own and copy this worksheet separately and not with another group of worksheets.
When you create a copy of a worksheet with a table, the table name in the duplicate worksheet will be given a suffix. For example, if the table was called Details, the duplicate worksheet table may be called Details2.
How to copy a worksheet or multiple worksheets to a different workbook
To copy one or more worksheets to a different workbook, select the tabs that you want to copy. To select multiple tabs, hold down control as you left-click on the mouse to select each tab.
Once you have selected the tabs to be duplicated, right-click to open the tabs menu.
From the tab’s menu, select Move or Copy. This will open the Move or Copy Options box.
Using the To Book dropdown, select the workbook you wish to place the duplicated sheets. You can also select a new book to open a new Excel workbook containing the duplicated sheets.
To copy the selected worksheets to the selected workbook, ensure you have included a tick on the Create a copy box and press ok.
What happens to Formulas and Formatting and Tables when you copy a worksheet to a different workbook?
When you copy a worksheet with formulas to a new workbook you can encounter problems. If you copy a worksheet with formulas that reference cells in a different worksheet in the original workbook and you do not copy the reference sheets too, then the formula in the duplicate sheet will contain the original sheet references.
Very often this problem is not spotted and can cause problems at a later stage. To ensure this does not happen when you are copying the worksheets that you also copy any sheets formulas might reference.
Formatting from original worksheets will copy across when you copy a worksheet to a different workbook.
If however a worksheet contains a table, then you can not copy this worksheet across in a group. You must copy this worksheet separately. This means if a worksheet with formulas references a table, you can not copy these as a group and the workbook reference will be included in any formulas that reference the table in the duplicated sheets.
What if you do not want to copy the reference sheets in formulas to a different workbook?
There are often when you do not want to copy all sheets to a different workbook. Yet at the same time, you do not want the workbook references to be added to the formulas in the duplicated sheets. Unfortunately, there is no simple trick to avoid this and we must create a workaround.
Use find and replace.
You can use Find and Replace to find all the workbook names and replace them. To this, press CTRL + F to open Find and Replace. Enter the worksheet name in the Find box and leave the Replace box empty. Select replace all and this will remove the sheet name from all the formulas.
Change source data.
If you have copied a worksheet from a different workbook and it contains links to sheets within the original workbook, you can change the source data to update these formulas to the current workbook.
To do this, first, save the workbook. Then from the Data Ribbon, under the queries and connections group select Edit Links.
The edit link option box will open.
Select Change Source and then navigate to the newly saved file and select the file. The workbook references to the old source will now be change and the formulas will no longer link to the original worksheet.
Over to you Now
there are other ways to copy worksheets, drop a comment below sharing a different method with us.
Have you had problems Copying and Duplicating Excel Worksheets? If so share some details with us and how you overcame the problem
Take A FREE course with us Today!
- includes XLOOKUP and will soon include Dynamic Arrays
I would suggest that you sign up directly for your own hive wallet and use this to sign into the comments below. This way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet
>> GET HIVE WALLET NOW<<
Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can contact us with this link.
Like what you see? I do hope that you will share this article across your social profiles
Do you want to start collecting rewards quickly for learning Excel? Then you should try:
10+ Excel Learn and Earn Activities YOU can do Today
Sign up for my newsletter – Don’t worry, I won't spam. Just useful Excel and Power BI tips and tricks to your inbox
SIGN UP
Your level lowered and you are now a Minnow!