How To Copy A Work Sheet Template In Excel

Excel users oft find themselves stuck on a treadmill of repetitive tasks. For example, y'all may demand to make a re-create of a worksheet to serve as a backup re-create or for testing purposes. In this article I'll describe three ways to duplicate a worksheet. Two involve menus, the second a mouse-and-keyboard trick, and the tertiary a single line of programming code.

You may already be familiar with the start arroyo shown in Effigy 1:

• Click Format on Excel's Dwelling house menu.
• Cull Move or Copy Sail.
• At this betoken the Move or Copy dialog box appears:
  1. Copying within the same workbook: Click Create a Copy so click OK.
  2. Copying to another workbook: Select the workbook name from the To Book list, click Create a Copy, and then click OK.
  3. Moving a sheet to another workbook: Select the workbook proper name from the To Volume list, and then click OK.

Figure 1: A traditional approach to duplicating worksheets within a workbook.

A 2d mode to duplicate worksheets involves right-clicking on whatever worksheet tab, and and then choosing Move or Re-create from the context carte du jour that appears. From there follow the same steps inside the Move or Copy dialog box as described higher up.

Every bit shown in Effigy 2, personally I notice it easier to use a third approach, which involves property down the Ctrl fundamental while I use my left-mouse button to drag a worksheet to the right. This action volition duplicate a worksheet without involving any menus.

Figure ii: Concord down the Ctrl fundamental while you drag a sheet tab to make a copy.

As shown in Figure 3, the fourth approach enables you to replicate a contiguous group of sheets:

• Click on the first worksheet you wish to replicate.
• Hold down the Shift primal while you select the last sheet that you wish to copy.
• Concord down the Ctrl key while y'all drag the first sheet in the group to the right with your left-mouse button. In this case y'all must select the first canvass in the group, otherwise you'll simply deselect some other sheet. Alternatively you can apply either of the first ii approaches discussed in this article once you've grouped the worksheets.

Figure iii: You lot tin replicate groups of sheets at once.

You'll likely only apply this fifth arroyo for special projects. Let'due south say you need to make 12 copies of a worksheet when setting up a budget spreadsheet, or permit'southward say 50 copies of a spreadsheet to track activity by location.

To test the concept, let's start with a workbook with a single sheet, with a goal of having 12 copies of the same worksheet. A one-line macro can make xi copies of the current worksheet:

1. Printing Alt-F11 on your keyboard to display Excel's Visual Basic Editor. Mac users should press Fn-Alt-F11. Although it looks like a separate plan, it's a hidden aspect of Excel that most users haven't seen earlier.
2. Click the View menu within the Visual Basic Editor.
3. Click Immediate Window from the View menu, or press Ctrl-Thousand on your keyboard (for Mac, Ctrl-Cmd-Yard).
4. At this point the Firsthand window will appear onscreen. This is a special area where whatsoever programming code you type volition be executed immediately; hence the name.
5. Blazon the following line of programming lawmaking into the Immediate window, exactly as written below, and then press Enter.

Do Until Sheets.Count=12: Activesheet.Copy,ActiveSheet: Loop

The line of programming code must announced as a single line within the Immediate window.

The downside of the Immediate Window is you don't get any direct feedback if your programming code worked, other than seeing that you lot now have 12 copies of the worksheet within the workbook. Error prompts will appear if you press Enter when the line of lawmaking is either incomplete or contains typographical errors. You may also encounter an fault if the workbook is protected past way of the Protect Workbook command on Excel's Review menu.

1. You tin safely exit the Visual Bones Editor once you lot've run the line of code.

Figure 4: Y'all can use a line of programming code to create equally many copies of a worksheet equally y'all need.

The same line of code utilizes Visual Basic for Applications in Microsoft Excel. This is known as an object-oriented programming language, so if you desire a little insight as to what the macro is doing:

• Do Until sets up a loop, significant Excel will repeat the actions until a status is met.
• Sheets is a collection of all worksheets within the workbook. This really includes other types of sheets every bit well, meaning Nautical chart Sheets and Macro Worksheets. We could be more specific and use the Worksheets collection instead, but Sheets results in less typing.
• Count returns the number of worksheets within the collection.
• = 12 is the ultimate number of sheets that yous want within the workbook. You'll adjust this to suit your needs, such as changing information technology to 6 if you wanted to replicate the worksheet 5 times. This tells the loop to repeat its procedure until the total number of sheets matches the number you specified.
• ActiveSheet.Re-create ,ActiveSheet instructs Excel to place a copy of the worksheet that you lot see on screen to the correct. If you omit the ", ActiveSheet" portion and then the macro volition create private workbooks that contain a re-create of the current worksheet.
• Loop instructs Excel to start the procedure over again. The Exercise Until instruction at the start means the loop will stop on its own when the total number of sheets in the workbook matches the number you specified.

If yous were to store this within a formal macro, the code might take this form:

              Do Until Sheets.Count = 12

              ActiveSheet.Copy ,ActiveSheet

              Loop

The Immediate Window only allows usa to execute a single line of code at a fourth dimension, so the colons allow us to string three lines of lawmaking together into a unmarried line that can be executed.

If needed there are two ways to determine the number of worksheets soon in an Excel workbook:

• In all versions of Excel type this in the Immediate Window and printing Enter: ?Sheets.Count    The Immediate window volition display the number of sheets in the workbook.
• In Excel 2013 and later enter this formula into any worksheet jail cell to determine the number of sheets in the workbook: =SHEETS()

How To Copy A Work Sheet Template In Excel,

Source: https://www.accountingweb.com/technology/excel/5-ways-to-duplicate-worksheets-in-excel

Posted by: longgonly1982.blogspot.com

Share this post